Tag Archives: job

SQL Server Job to Capture Reads

We needed to capture physical reads over time from our SQL server.  One method we decided to deploy was to check sys.dm_exec_query_stats periodically and capture the differences between each check.  The way I am doing it below is not 100% accurate because sys.dm_exec_query_stats is based on cached plans and the server will let clear the plan data over time.  This means that when the differences are calculated any plan that was cleared from cache would not be included in the current check, but would still be in the comparison record.  This could be solved by storing and comparing at the plan level but for my purposes I prefer to store summary information and do not mind this issue because I am running my comparison every hour and then will be averaging the results over time, and the result does not need to be exactly precise, just close enough to give us a general understanding.

This is the query inside the job:

This is what the table looks like with a few records.  Note that this is a result of running the job multiple times with a few seconds between each interval.

SQL Server Job to Capture Reads result

Disable then Enable SQL Agent Jobs

This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.

Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected


Get and Kill Executing Processes of Job

This query will identify the current SQL processes or executions which belong to a specific job.

We had a job process that was getting blocked for long periods, and in turn was causing a lot of blocking.  The best solution here is to fix the root blocker, however, as a short term fix we want to allow the job to run but then kill it a specific time if it is still running.  To do this, we create another job that looks for all of the SPIDS associated to executions which belong to the job getting blocked, and execute a kill commend for each SPID.