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

List SSAS User Roles Using PowerShell

Here’s the PowerShell script that can be use to list users and their roles in SQL Server Analysis Service (SSAS) database using PowerShell and Analysis Management Object (AMO):

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"

$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles | Select Name, Members

Notes (25th Apr 2013): Made a correction to one of the variable name on the code. On line 8, instead of using $SSASServer, it should use $SSASDB.

Contact Form

As part of the process of redesigning this blog, I’ve decided to add a contact form page to this blog. Contact form would allow lovely readers, such as yourself, to communicate (or contact) me directly. It would certainly allow me to “listen” to your comments, critics and feedbacks.

Obviously I can share my e-mail address on this blog. However, that would potentially expose my e-mail address to spammers, which I prefer to avoid. Contact form would address this problem. There are several online applications that would allow you to easily create online forms and integrate those forms to your blog. After doing a little bit of research, I chose to use Wufoo (@Wufoo). Wufoo is part of SurveyMonkey, which is an established company that provide easy to use online survey. There are several factors that lead me to Wufoo:

1. It’s free … well sort of. Wufoo has Gratis plan, which is free of charge. However, there are some limitations, such as we only get 100 entries/month and we can only build 3 online forms. At this point, I can live with those limitation. For more information on Wufoo’s plans, click here.
2. Ease of use. It is very easy and intuitive to create the online form (in my case, the contact form). I think the GUI of their form builder tool is well-designed. I can drag and drop the fields that I want to have in the form. I can also easily define the field properties, such as whether or not the field is a required field, the maximum number of characters that can fit in that column, etc.
3. Easy implementation to the blog. Once the form has been created, there is a theme designer in Wufoo that allows us to modify the form look and feel to match our blog theme (font, color, etc.).                  Once the form has been created, Wufoo would generate the script(s) that we can use in our blog. I am using blogger as my blog engine. To implement the contact form, I would create a new page, and copy and paste the Javascript version of the Embed Form Code.
4. Report and notification options. With Wufoo, we can easily set up e-mail notification whenever someone submitted an entry to the form. We can even set up alert to our mobile phone whenever someone submitted an entry. In addition, the entries are also being recorded in their database. So we can also login to Wufoo and generate a report that would list all the entry.
5. More than just a contact form. Currently I am just using Wufoo for contact form. However, I can certainly see more usage possibilities on this blog, such as online poll form, survey form, etc.

Overall I am pretty happy with Wufoo so far. The only issue that I might have is the form loading time might be a tad slower than I expected. For those who is looking to implement any kind of online form (either contact form, registration form, survey, etc.), I would recommend to give Wufoo a try.