Server Side trace for Blockings not working as expected!!

Question

Server Side trace for Blockings not working as expected!!

rollercoaster43

Ten Centuries

Points: 1306
More actions
September 17, 2013 at 2:58 am

#278014

Hi All,
I wanted to check if there has been any blockings on my database and I am using the below server side trace in my sql Agent job.
while I was exporting the trace definition, I could not get the filters in the script so I added a filter to include only a particular Database (Adventureworks2008).
However, I can see that this trace captures blocking of other databases as well(I have deployed this server side trace on a prod server after the definition was exported to a file)
Moreover, I do not see much information begin captured about the code that is being blocked and the code that is causing the blockings.
Is there anything missing in my trace definition ? Am I doing something wrong?
I am using the same trace definition to schedule a job on Prod servers(2005/2008/2008R2). Will that make any difference?
Thanks!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Your issue is that the Blocked Process Report Event does not include the DatabaseName column, so you can't filter on it for that event. You need to use the DatabaseID column for your filter.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rollercoaster43 Ten Centuries Points: 1306 More actions · Answer 2

Thanks for pointing that out Jack. I was sure that I was missing something!!

I have attached the trace textdata that I had captured which doesn't give me much of information about the blocking code, except the blocking login.

Any suggestions with additional events so that I can atleast get the blocking code?

Thanks !!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You can start with this article.

If you want to see the information in your trace you probably need to add sql:batch_starting and sql:batch_completed.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

In your example, there is:

This can be translated to SQL code with

SELECT object_name(objectid),

stmt = substring(text, 56510/2, (56610 - 56510)/2)

FROM sys.dm_exec_sql_text(0x03002b009fbde449363f36010ea200000100000000000000)

The report includes the full call stack, and I suspect that I grabbed an EXEC statement here.

Note that the blocking statement is necessarily not present in the report. The blocker may be running a multi-statement transaction, and the lock may come from a previous operation.

Of course, translating the stack frames manually is tedious code. I wrote some nifty code to analyse a trace of blocking-process reports, but unfortunately I don't have this code at home.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]