Stopping Your SQL Server Jobs

Maintenance in General is a necessity for SQL Server. No different than changing the oil in your car or going to the Doctor for the annual exam. There are going to be times when you need to perform maintenance on your server. The tricky part is trying to determine when you should start the maintenance so that it completes before the busy time. The common approach to this problem is to simply determine how long a job executes (often determined by trial and error) and then adjust the start time to give the job enough time to execute. There is another way…

SQL Server has a number of system stored procedures that you can use to perform tasks that you might be doing in the user interface, for example… If you want to stop a job you can open SQL Server Management Studio, navigate to the job, right click and stop the job. Here is where the system supplied stored procedure comes into play. What if your busy time of the day is at 6 AM, and you want to make sure that the indexing has finished by 5:00 AM so that the system is ready to take on the day. Do you really want to wake up at 5:00 AM just to right click and stop job, in the chance that it is running?

Simply schedule a job that will execute at 5:00 AM (the time you want to make sure the maintenance job is done by), and create a step that will stop the job.

exec sp_stop_job @job_name=N'My Job Name'

Fairly simple. But, what if you want to add some logic to the job so that not only does it just try to stop the job it will check the job to determine if it is executing first? And now that we are looking at some of the options there are, we should put a line of code in there that will email us whenever the maintenance job has run long and had to be stopped.

Select name
from msdb..sysjobs j
 join msdb..sysjobactivity a on j.job_id = a.job_id and j.name = 'My Job Name'
Where start_execution_date is not null and stop_execution_date is null
If @@rowcount > 0
      Begin
            EXEC msdb.dbo.sp_stop_job @job_name = 'My Job Name'
            EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MyMailProfile', @recipients = '[email protected]',
            @body = 'The Indexing Rebuild Job had to be stopped due to long run time.', @subject = 'Index Rebuild' ;
      End
Else Return

I hope this tip has helped you in one fashion or another. If you would like my list of TOP 10 TIPS FOR SQL SERVER PERFORMANCE AND RESILIENCY can be found here with Tip # 1.