SQL Server Jobs – Using begin tran Instead of use db_name for Stored Procedure

jobssql server

I was defining a SQL job yesterday for executing stored procedure at a specific time in the morning. It was an ad-hoc and urgent requirement and hence I developed this procedure on the fly and scheduled it to run every morning.

I was supposed to be just calling the procedure in the job step and define output file in the advanced tab. I had to use below command inside the job step:

use db_name
go
exec sp_name
go

Instead of above, I wrote as below by mistake:

begin tran
go
exec sp_name
go

This sql job didn't fail however it didn't do its intended job.

Output file contains below:

Job 'Job_Name' : Step 1, 'Step_name' : Began Executing 2020-02-03
06:10:00

Which seems to be correct however I was wondering that above could keep an open transaction and could cause another issues.

I executed sp_whoisactive(Thanks to Mr. Adam Machanic) and don't see any session running from that time as well as there is no blocking on any table as underlying procedure was having tablock table hint while inserting data into the table.

Is there anyway to find what actually happened to this sql job and why did it not do its intended work as well as if there is anyway to find whether it is still running in the background and could be committed or rolled back. I ran sp_who2 also to see if there is any SPID associated to this job, couldn't find anything.

Ran below query to check the status of all jobs and it shows my job succeeded:

USE MSDB
SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,MAX(DATEADD(S,(run_time/10000)*60*60 /* hours */  
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113))),100) AS [Time Run]
         ,CASE WHEN enabled=1 THEN 'Enabled'  
               ELSE 'Disabled'  
          END [Job Status]
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'  
          END [Job Outcome]
FROM   sysjobhistory SJH  
JOIN   sysjobs SJ  
ON     SJH.job_id=sj.job_id  
WHERE  step_id=0  
AND    DATEADD(S,  
  (run_time/10000)*60*60 /* hours */  
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
  + (run_time - (run_time/100) * 100)  /* secs */,  
  CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-20,GetDate())  
  group by name, CASE WHEN enabled=1 THEN 'Enabled'  
               ELSE 'Disabled'  
          END 
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'  
          END

Any help or input is highly appreciated.

Version: Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) – 12.0.6108.1
(X64) May 29 2019 20:05:27 Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
(Build 9600: ) (Hypervisor)

Best Answer

When you do that in a job it basically ends the session and rolls back the transaction when the job finishes. You can trace that using SQLTransaction event class in Profiler. That's the reason why you do not see any open transaction/sessions or the work being done.

enter image description here

Related Question