SQL Server Agent Job Last Run Date and Time from Sysjobsteps Table

A couple of weeks ago, I wrote a blog entry in regards to finding out the last successful run date and time of SQL Server Agent job by querying the sysjobhistory table in the msdb database. That blog entry can be found here. The sysjobhistory table stores the execution history of each SQL Server agent job and job steps.

There is another way in which you can get the last run date and time of the SQL Server agent jobs (well, kind of). You can do this by querying the sysjobsteps table in the msdb database. The sysjobsteps stores information of each SQL Server job step, including the last_run_outcome (whether the job step execution failed, succeeded, retry, canceled or unknown), the last_run_date and the last_run_time. Similar to the run_date and run_time columns in the sysjobhistory table, the last_run_date and the last_run_time columns in sysjobsteps have integer data type. Thus, to convert it to date and time, we can use the agent_datetime system function in the msdb database. Unlike, the sysjobhistory, there is no step 0 in the sysjobsteps table, which would give the overall job outcome.

An sample query that we can use to retrieve the last run date and time for each SQL Server job steps:

select j.job_id,
  j.name,
  js.step_id,
  js.step_name,
  last_run_outcome = case when js.last_run_outcome = 0 then 'Failed'
        when js.last_run_outcome = 1 then 'Succeeded'
        when js.last_run_outcome = 2 then 'Retry'
        when js.last_run_outcome = 3 then 'Canceled'
        else 'Unknown'
       end,
  last_run_datetime = msdb.dbo.agent_datetime(
       case when js.last_run_date = 0 then NULL else js.last_run_date end,
       case when js.last_run_time = 0 then NULL else js.last_run_time end)
from msdb.dbo.sysjobs j
  inner join msdb.dbo.sysjobsteps js
   on j.job_id = js.job_id;

The last_run_outcome can be a bit misleading. If the job step has not been run, the last_run_outcome is 0, which indicates that the job step has failed on its last run.

Purging the SQL Server agent history (see my blog post) would not remove the last_run_date and last_run_time value from the sysjobsteps table.

Purge SQL Server Job History

The msdb database stores SQL Server job information, including the job and job steps history. The job history information can grow really fast, especially if you have a lot of SQL Server jobs which run frequently. In turn, this can cause the msdb database to grow in size rather quickly. It is a good idea to purge old SQL Server job history on regular basis. You might want to check with your company policy or legal team on how much of job history you need to keep.

How do you go about purging the SQL Server Job History? There are several ways in which you can do this.

sp_purge_jobhistory

The sp_purge_jobhistory stored procedure in msdb database can be used to delete the SQL Server agent job history. It accepts 3 input parameters:

• (Optional) @job_id - the SQL Server job id of the job history that you want to delete

• (Optional) @job_name - the SQL Server job name of the job history that you want to delete

• (Optional) @oldest_date - delete SQL Server job history that is older than the @oldest_date

One thing to know is that when running the sp_purge_jobhistory, you can specify the job_id or job_name or neither of them. But you can not specify both the job_id and job_name. If you do, you will most likely get the following error message:

"Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25
Supply either @job_id or @job_name to identify the job."

Some sample usage:

To delete all job history:

exec msdb.dbo.sp_purge_jobhistory;

To delete all job history of SQL Server job named "Backup TestDB":

exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB';

Alternatively:

declare @job as uniqueidentifier;
set @job = (select job_id from dbo.sysjobs where [name] = 'Backup TestDB');
exec msdb.dbo.sp_purge_jobhistory @job_id = @job;

To delete job history that is older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @oldest_date = @jobdate;

To delete job hisotry for SQL Server job named "Backup TestDB", which are older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB', @oldest_date = @jobdate;

It is pretty straight forward. To do this on regular basis, you can create a SQL Server agent job to run the sp_purge_jobhistory with the appropriate input parameter(s) on a scheduled time.

SQL Server Agent Properties

If you go to the SQL Server Agent Properties window, you have the option to "remove agent history". Here, you need to specify the cut-off time.

If you try to script this out, you will see that it is generating the following script:

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2013-02-12T22:54:55'

So basically it is executing the same sp_purge_jobhistory stored procedure on the background. One thing to remember about purging the job history this way is that it is just one time only. It will not actually create any SQL Server Agent that would automatically delete job history that are older than 4 weeks.

syspolicy_purge_history SQL Server Job

By default, if you are using SQL Server 2008, 2008 R2 or 2012, you might see the syspolicy_purge_history SQL Server agent job. The job is scheduled to run daily at 2 AM. This SQL Server job would look into the Policy Management "HistoryRetentionInDays" property (You can get to this window by going to Management on the Object Explorer and then right click Policy Management and select Properties).

By default, the HistoryRetentionInDays is set to 0, which means that it will not delete any SQL Server job history. If you set it 30, then on daily basis at 2 AM, it will delete SQL Server job history that are older than 30 days.

Last Successful SQL Server Agent Job Run Date and Time

SQL Server stores SQL Server Agent job history in sysjobhistory table within the msdb database. We can query the table to get the date and time of last successful SQL Server agent job(s) run. The table stores each job step history, where step_id 0 for each job actually give the overall job outcome.

The sysjobhistory table also stores each step's run date and time. However, they are being stored in separate columns, run_date and run_time. Also instead of having date and/or time data type, they have integer data type. But don't worry, there is a system function in msdb database, called agent_datetime, which can be used to convert those run_date and run_time into a datetime data type.

So, to get the date and time of the last successful SQL Server Agent job(s) run, we can use the following script:

WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status)

SELECT    j.job_id,
          j.name,
          jh.run_status,
          jh.last_run_time
FROM      msdb.dbo.sysjobs j
          LEFT OUTER JOIN jobhistory jh
          ON j.job_id = jh.job_id
ORDER BY j.name ASC

The script should be pretty straight forward. Basically, first we build a CTE to get the last successful time of each job from the sysjobhistory table. We do this by filtering those SQL Server agent jobs that have run status equal to 1 (success) for job step with id 0. Once we do that, we then create a join of the sysjobs table (which contains information such as the job name), with the CTE.

Installing 32-bit SQL Server on 64-bit Windows (x64)

Looking at the hardware and software requirements for SQL Server 2008, 2008 R2 and 2012 from Microsoft site, we should be able to install 32-bit version of SQL Server onto 64-bit (x64) Windows operating system.

If you have installation media or ISO image that contains both 32-bit and 64-bit version of SQL Server 2008, 2008 R2 or 2012, here’s how you can install the 32-bit SQL Server on 64-bit Windows (x64). When you insert the installation media, or mount the ISO image, in some cases you might need to double click on the setup.exe, you will be presented with the SQL Server Installation Center. Click on the Options link on the left.

You should then be able to select to install the 32-bit (x86) of the SQL Server.

You can kick off the 32-bit installation process, by running the following command:

setup.exe /action="install" /x86=true

As a matter of fact, you can use the X86=true in the command line or answer file if you want to have unattended installation of SQL Server 2008, 2008 R2 or 2012.

Once it has been installed, when you check the SQL Server version using:

SELECT @@VERSION

For SQL Server 2012 installation, you should get something like the following:

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)Feb 10 2012 19:13:17Copyright (c) Microsoft CorporationEnterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)

Noticed that it is WOW64 (Windows 32-bit on Windows 64-bit).

Deadlock Graph XML from Extended Events in SQL Server 2008

We were dealing with some deadlock problems in our database. However, unfortunately we did not have trace flag 1204 and/or trace flag 1222 turned on. Fortunately there is a way to retroactively retrieved deadlock information starting with SQL Server 2008 using Extended Events.

Someone forwarded an article written by Jonathan Kehayias (blog | twitter), an expert in Extended Events, regarding this. The article can be found here. It is a great article. In the article, Jonathan showed us the way to retrieved the deadlock graph from Extended Events.

Jonathan also mentioned in the article that there is a bug in the output of deadlock graph from the Extended Events, in which it is not a valid XML. There is a problem with missing end tag for the <victim-list> node. He then provided a work-around script in which he utilizes the replace function on the deadlock information text from the Extended Events. Then after that we could cast the text as XML.

I went ahead and try that script. When I tried it, I got the following error message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 5, character 17, end tag does not match starting

Upon further review, it looks like the script works on deadlocks that have exactly 1 <victimProcess> node. However, if there is empty <victimProcess> node or multiple <victimProcess> nodes, the script would not work. In the case of multiple <victimProcess> nodes, for some reasons, the Extended Events only closed the last <victimProcess> node, but all the other ones are missing the end tag. Thus, I made a small tweak to Jonathan’s script to handle empty <victimProcess> node or multiple <victimProcess> nodes. Here’s the script:

SELECT    CAST(REPLACE(

            REPLACE(
                REPLACE(

                    REPLACE(

                        REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

                        '<victimProcess', '</victimProcess><victimProcess'),

                    '<victim-list>', '<deadlock><victim-list><victimProcess>'),

                '<process-list>', '</victim-list><process-list>'),

            '<victim-list/>', '<deadlock><victim-list>'),

        '<victimProcess>' + CHAR(10) + SPACE(2) + '</victimProcess>', '') AS XML) AS DeadLockGraph

FROM

(SELECT CAST(target_data as XML) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

WHERE name = 'system_health') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'