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'

SQL in the City: Boston

A few weeks ago, I got the chance to go to a day of free SQL training event that was organized by Red Gate Software. The event was called SQL in the City. It was conducted on October 8th, 2012 at Harvard University's The Joseph B. Martin Conference Center. It had a pretty good turnout, considering that it was a Columbus day holiday (for some people).

The training covered various topics, from SQL Server maintenance, monitoring, development, and others. The training sessions were being presented by SQL Server MVPs, such as Steve Jones (blog | twitter), Grant Fritchey (blog | twitter) and Adam Machanic (blog | twitter). They really passionate and more importantly knowledgeable about SQL Server. They also were very approachable. They made themselves available for questions even after their sessions, so we can interact with them, which was awesome.

One of the sessions that I found interesting was "Architecting Hybrid Data Systems with SQL Server and Windows Azure". It was presented by Buck Woody (blog | twitter). Obviously, "Cloud" is now the buzz word in IT. Buck pointed out, which I agree totally, that before moving to the "Cloud" we should think about the current problems that we have, and if by moving to the "Cloud", it would help to resolve those problems.

There were also a lot of Red Gate people in the events. I was able to interact with some of them and learned more about their tools/ software, some of which that I did not know about. Tools such as SQL Storage Compress, which I am looking forward to test and might be useful to save some (… well hopefully large amount of) disk space in our testing environment.

Overall, it was well organized event. It was a good day for me to network and also to learn more about SQL Server and Red Gate products. Thank you Red Gate. Hopefully, this can be an annual event in Boston.

Querying SQL Server Error Log

SQL Server error log offers good way to obtain information when troubleshooting SQL Server related problem. A while back, I wrote a blog post about reading SQL Server error log using Microsoft Log Parser. That blog post can be found here. There are many ways in which you can query the SQL Server error log. One of them is using the sys.sp_readerrorlog stored procedure. This stored procedure can be located in the master database. It accepts 4 input parameters:

1. @p1 – integer: This parameter is to specify which error log to read. SQL Server error log would rollover. By default, SQL Server error log would keep a file for the current log and maximum 6 of archived logs (this setting can be changed easily), ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4, ERRORLOG5 and ERRORLOG6. ERRORLOG is where SQL Server stores the current error log, ERRORLOG.1 is where SQL Server stores the most recent archived, etc. If we put 0 or null on this parameter, we are querying the current error log (ERRORLOG). 1 would refer to ERRORLOG.1. The same concept would apply to SQL Server Agent error log.
2. @p2 – integer: This parameter is to specify if we want to query the SQL Server Error Log or the SQL Server Agent Error Log. If we enter 1 or null, we are querying the SQL Server Error Log. However, if we enter 2, then we are querying the SQL Server Agent Error Log.
3. @p3 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log.
4. @p4 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log. If we enter a word or phrase on @p3 parameter and enter another word or phrase on @p4 parameter, the stored procedure should return error log entries that contain both words/phrases (AND operator). If we leave @p3 blank but enter a word or phrase on @p4, the stored procedure would not filter the error log. It will ignore the @p4 parameter filter.

Some Usage Examples

The following would return all entries on the current SQL Server error log (ERRORLOG):

EXEC sp_readerrorlog

or:

EXEC sp_readerrorlog 0

or:

EXEC sp_readerrorlog NULL, NULL, NULL, NULL

The following would return all entries on the current SQL Server Agent error log (SQLAGENT.OUT):

EXEC sp_readerrorlog 0, 2

The following would return entry from SQL Server error log when the SQL Server was starting the msdb database (in this case it was part of the server start up):

EXEC sp_readerrorlog 0, 1, 'starting', 'msdb'

This would returns:

Wait, There’s more…

If we look at the sp_readerrorlog stored procedure code closely, it is actually calling the xp_readererrorlog extended stored procedure. The xp_readerrorlog actually accepts more input parameter than the 4 input parameters described above. The following blog article described the parameters that xp_readerrorlog would accept. Basically it would accept 3 additional parameters:

1. Log date from range – Date time: this parameter would help to filter the log entries from a specific time period.
2. Log date to range – Date time: this parameter would help to filter the log entries to a specific time period.
3. Ascending or Descending – Varchar: this parameter can be use to specify the sorting order of the log entries based on the log date. Enter ‘asc’ for ascending order, and ‘desc’ for descending order.

So, for example, if we want to get the list of current SQL Server error log entries between 6:27 PM and 6:28 PM today (7th October 2012), and list the log entries in the descending log date order, I can use the following query:

EXEC xp_readerrorlog 0, 1, NULL, NULL, '2012-10-07 18:27', '2012-10-07 18:28', 'desc'

Related Notes:

• Querying SQL Error Log with Microsoft LogParser
• Querying SQL Server Agent Error Log with Microsoft LogParser