SQL Server Management Studio Standard Reports - Top Transactions by Locks Count - Carpe Datum - Site Home

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today I'm covering a report that shows more about locking activity, by transaction.

Before I begin describing the columns and values in this report, it might make some sense to talk a little about the locking and blocking in the database engine. I've covered this in some of the other reports, but I wanted to point out some links that might be useful about locks:

The basics: http://msdn2.microsoft.com/en-us/library/ms172429.aspx
General Locking info: http://msdn2.microsoft.com/en-us/library/ms190615.aspx
The activity monitor that shows a lot of real-time locking information is here: http://msdn2.microsoft.com/en-us/library/ms188948.aspx
Want more information on finding locks? http://msdn2.microsoft.com/en-us/library/ms177475.aspx
More about dynamic locking here: http://msdn2.microsoft.com/en-us/library/ms189286.aspx

OK - down to the task at hand: This report shows locks by transactions, and the first band groups everything by Transaction ID:

Transaction ID	The ID of the Transaction that is locking.
Transaction Name	Name of the transaction, if set. Otherwise, this is set to "user_transaction".
State	Whether this transaction is active or not.
Transaction Type	Whether this is a Full or partial transaction.
Start Time	The time this transaction started locking.
Isolation Level	There are several types of “isolation levels” for the lock activity. You can read more about those levels here.
# Locks	The number of locks in this session.
Session ID	The session ID of the transaction - this is the number that uniquely identifies the statement that also shows up when you do an sp_who2 statement.
Login Name	The name of the login associated with this session.

The next band groups the information by Database Name:

Database Name	The database where the locking is taking place.
Transaction State	Shows whether the session is active or not.
First Update Time	The time this transaction was first updated.
# Locks	The number of locks on this particular database.

Another band below that shows the Resource Name. This is the object being locked. Below that is a row for each type of lock taken:

Lock Type	There are several lock types (also called “granularities”), which you can read more about here.
Request Mode	The mode of lock being requested. More here.
# Locks Granted	The actual number of locks.
# Locks Waiting	The number of locks that are waiting for a lock.

The final band shows a part of the Currently Executing SQL Statement. This might be blank if the locks are system-generated.