July 16, 2012 at 3:19 am
Hi Team,
How can we monitore Blockings in Instance level please suggest me. I m expecting all the databases blockings in one table only
Tx
July 16, 2012 at 3:28 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 3:28 am
blocks or locks?
sys.dm_exec_requests will show you who is blocking by the blocking_session_id
sys.dm_tran_locks will show you what locks are help against objects
July 17, 2012 at 10:27 pm
Activity Monitor can help you if you have sql 2005>.
July 19, 2012 at 9:01 am
Here is a link to an excellent script to find sessions that are blocking others. Has come in handy several times for me.
http://www.sqlservercentral.com/scripts/Head+Blocker/75366/
One time, a user started a report run and went to lunch. The other users couldn't insert any new records to the transaction tables because the report process was locking the tables. One quick run of this told me exactly who was causing the issue and it was resolved quickly.
One change I made was to order first by [Database], then by s.session_id
That way it is easier to find issues if you know you are looking for blocking in a certain database.
Hope it helps.
July 19, 2012 at 11:21 am
sp_whoisactive is a great option too. you can find the latest version (11.11 last I heard) on sqlblog.com.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply