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)
dbcc opentran
on the database on which the SP was run.