While continuing to look at what is causing blocks, you come across another format of information in the waitresource column in sysprocesses. This time you see:
PAG: 7:1:11223344
Again - thanks for the help SQL - object_ID too much trouble for you I guess?? Anyway, gives a DBA something to do I suppose.
This time it's not a hobt_id, but a page id, and is read as "DB_id, file_id, page_id"
To get this one into text, we need to do a couple of things.
First Run:
DBCC TraceOn (3604)
- this just tells DBCC commands to output to the screen rather than errorlog, and just lasts for your current connection, otherwise the next command just tells you "DBCC execution completed" which is even less helpful than "PAG 7:1:11223344"!
Then run
DBCC PAGE (7, 1, 11223344)
(Note you could run this adding WITH TABLERESULTS for an even tidier output)
From the result set returned, you should be able to spot a few useful Id's, such as
Metadata: IndexId
Metadata: ObjectId
From here, with OBJECT_NAME() or just scanning sys.indexes, you should be able to ascertain what table and index you are being blocked by.
2 comments:
Awesome post
This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It?s the old what goes around comes around routine. Did you acquired lots of links and I see lots of trackbacks??
Have you ever considered adding more videos to your blog posts to keep the readers more entertained? I mean I just read through the entire article of yours and it was quite good but since Im more of a visual learner,I found that to be more helpful well let me know how it turns out! I love what you guys are always up too. Such clever work and reporting! Keep up the great works guys Ive added you guys to my blogroll. This is a great article thanks for sharing this informative information.. I will visit your blog regularly for some latest post.
http://www.sqlservermasters.com/
Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun writing this article.
http://www.mindqonline.com/
Post a Comment