Stopping your SQL Server Job

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 is going to be times when you are going to need to run 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’

Not to complex. 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 = ‘Me@xtivia.com’,

@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.

 

Advertisement

What is missing in SQL Server 2008?

Here is what I find interesting. For each install of SQL Server you can have a ton of databases that could be on a system. Let’s look at a reporting server as an example. I know of one reporting server that houses over 52 databases. Most of this is feed via replication. Every time this company hires a new user we would have to go in and grant access to each of the databases, if you do this in the user interface you are looking at a large number of Data/Reader access. Now the obvious answer here is that you can write a script that you just pass in the login name and then a custom level of access is granted to the database.

What I don’t understand is why SQL Server does not allow you to create a server level role. I would thing that since you can place many databases on one server that you should be able to have the access to grant Data Reader across a whole system in the user interface. I am sure that there are reasons that they are not doing this I just can not put my finger on why. End result is I am not seeing this in SQL Server 2008 yet and I don’t think it will be there upon the final release. Do you have the same issue, if so what do you think about them not adding this? What do you think is missing in SQL Server 2008?

Thanks,

Chris