In a big SQL environment, it is very common to see servers rebooted during maintenance. Though we might inform the users about the server maintenance, many DBAs miss taking care of their SQL Server agent jobs. In this article, we will learn how to identify SQL Server Agent jobs that are scheduled to run at a specific time. We can then disable jobs whose schedule collide with the maintenance window.
You might have set up SQL Server jobs to send out failure notifications or write to the event log soon as it fails. If SQL Server jobs are running and SQL Server service gets restarted, all those running jobs might become static. Although these jobs are not being executed, the status would still be “executing”. Since the job’s status is not “Failed”, you will not receive failure notification too. In such scenarios, many DBAs will either try to stop those SQL Server jobs or restart the SQL Server agent or SQL Server service or even try rebooting the entire server.
To avoid SQL Server jobs getting stopped abruptly during maintenance, we can proactively find jobs that might be running during the maintenance window and can disable those jobs and rerun them again after the maintenance. In this script, we are creating two tables. In the first table, we are reading and storing the data from the sysjobs table of msdb database. This table provides the start time and run duration in seconds but does not provide the end time. So we are creating a second table and using dateadd function so that we can find the end time for each job. We can then filter records from table2 to identify all jobs that collide with our maintenance schedule.
CREATE TABLE JBTBL1 (c1 VARCHAR(500), c2 DATETIME, c3 INT ); CREATE TABLE JBTBL2 (c1 VARCHAR(500), c2 DATETIME, c3 DATETIME, c4 INT ); DELETE FROM JBTBL1; DELETE FROM JBTBL2; GO INSERT INTO JBTBL1 SELECT systbl1.name, msdb.dbo.agent_datetime(run_date, run_time), ((systbl2.run_duration / 1000000) * 86400) + (((systbl2.run_duration - ((systbl2.run_duration / 1000000) * 1000000)) / 10000) * 3600) + (((systbl2.run_duration - ((systbl2.run_duration / 10000) * 10000)) / 100) * 60) + (systbl2.run_duration - (systbl2.run_duration / 100) * 100) FROM msdb.dbo.sysjobs systbl1 INNER JOIN msdb.dbo.sysjobhistory systbl2 ON systbl1.job_id = systbl2.job_id WHERE systbl2.step_id = 0; GO INSERT INTO JBTBL2 (c1, c2, c3, c4 ) SELECT c1, c2, DATEADD(s, c3, c2), c3 FROM JBTBL1; GO DECLARE @actstart DATETIME; DECLARE @actend DATETIME; SET @actstart = '2011-05-19 10:16:10'; SET @actend = '2016-05-19 10:16:10'; SELECT * FROM JBTBL2 WHERE @actstart BETWEEN c2 AND c3 AND @actend BETWEEN c2 AND c3;
You can tweak the script to send the result as HTML email to the DBA group or add additional TSQL statements to connect to target servers and disable those jobs. As an alternate method, you can stop the SQL agent before the maintenance and start it later. As you can notice in the script, we are referring the SQL Server’s msdb database for job’s data. If you are not able to retrieve SQL job information from msdb database, your database might be having a damaged mdf file.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook and excel recovery software products. For more information visit www.datanumen.com