Using SQL Profiler to trace locks

Question

Using SQL Profiler to trace locks

glynbach

SSC Veteran

Points: 285
More actions
July 22, 2005 at 8:03 am

#89206

Hi
I am using SQL Profiler to trace the sql statements an application server is sending. In particular I would like to see what types of lock are acquired at what stage. The only info I can seem to get is from the EventClass, which is only Lock:Acquired or Lock:Released. What I really need is to see the lock type in this trace. I would like to get an idea of when and why locks are being made at row, page and table level. Can anyone tell me how I can get more trace information on this?
Thanks
Glyn
July 25, 2005 at 8:00 am

This was removed by the editor as SPAM

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

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

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

I don't know that it can be done directly from Profiler, however Microsoft has a KB on creating a stored procedure that when executed will provide more comprehensive information on locks and latches. You can find this here:

http://support.microsoft.com/kb/271509/

There's also a sample script to set the sp to run in a loop located on the same page.

Hope this helps.

My hovercraft is full of eels.

glynbach SSC Veteran Points: 285 More actions · Answer 2

Ok. Thanks for the link. When used in conjunction with the profiler it should help.

ThanNhi Nguyen SSC Enthusiast Points: 152 More actions · Answer 3

Hi,

I need a record of who log in/out of the database, but didn't know how to do it, so I used SQL Profiler, but when I save the trace files into tables, the event class were listed as numbers (in the trace file it listed as login/logout, etc.). Could anyone please help?

Thanks,

tn