I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears, click the Standard Reports, and then select the title at the top of this post.
This report shows information similar to the Locking and Blocking reports I’ve covered in the other posts in this series, but since this is a database-level report, it only focuses on one database at a time, and it gives you more lock information than some of the server-level reports.
The first band groups the blocking information by the Transaction ID, which is a unique number assigned to each unit of work the server is doing. You can use this number to locate lots of other information in many of the Dynamic Management Views or other Meta-Data views:
|Transaction ID||A unique number assigned to every transaction – you can use this to join on other meta-data|
|Transaction Name||The name of the transaction assigned by the developer, otherwise the first part of the first statement|
|State||Whether the statement is active or not|
|Transaction Type||The type of transaction – Read only, read/write, or a system transaction|
|Start Time||The time this transaction started|
|First Update Time||The time this transaction was first updated|
|Isolation Level||The Transaction Isolation Level, if known. You can find out more about those here|
|# Locks||The number of locks this transaction is taking|
|Session ID||The unique ID of the session, which you can link to other meta-data views|
|Login Name||The Windows or SQL Server account associated with this transaction|
The second band starts a grouping for the Resource Name. This name shows the table or other database object that is involved in the transaction.
Below each resource being locked, you’ll see the following columns:
|Lock Type||This is the type of lock that is being taken, such as PAGE and so on. You can read more about these lock types here|
|Request Mode||Whenever the server needs to take a lock, it asks for it in a certain way. This is called the “Request Mode”, and you can read about those here|
|# Locks Granted||How many locks are granted on this resource name within this transaction|
|# Locks Waiting||The number of locks still needed by the transaction but not yet granted|
The last band shows the partial contents of the Currently Executing SQL Statement.