Microsoft SQL Server 2008 R2 Unleashed- P142:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1354 CHAPTER 37 Locking and Performance end as object convert varchar 12 resource_type as resrc_type convert varchar 12 request_type as req_type convert char 1 request_mode as mode convert varchar 8 request_status as status from order by request_session_id 3 desc go spid db_name object resrc_type req_ type mode status msdb 0 DATABASE LOCK GRANT 55 bigpubs2008 titles OBJECT LOCK I GRANT 55 bigpubs2008 sales_qty_rollup OBJECT LOCK X GRANT 55 bigpubs2008 sales OBJECT LOCK X GRANT 55 bigpubs2008 679707671068672 PAGE LOCK I GRANT 55 bigpubs2008 679707671068672 KEY LOCK X GRANT 55 bigpubs2008 398232694358016 KEY LOCK X GRANT 55 bigpubs2008 398232694358016 PAGE LOCK I GRANT 55 bigpubs2008 0 DATABASE LOCK S GRANT 56 msdb 0 DATABASE LOCK S GRANT 58 bigpubs2008 sales OBJECT LOCK I WAIT 58 bigpubs2008 0 DATABASE LOCK S GRANT Note that the query in Listing contains a CASE expression for displaying the object name. If the resource type is OBJECT and the database ID of the locked resource is the same as the current database context it returns the object name otherwise it returns the object ID because the object_name function operates only in the current database context. TIP To save yourself the trouble of having to type in the query listed in Listing or having to read it in from a file each time you want to run it you might want to consider creating your own stored procedure or view that invokes this query. NOTE In SQL Server 2005 you could monitor lock activity via the Activity Monitor in SQL Server Management Studio SSMS . If you re looking for this tool in SQL Server 2008 you won t find it. For some reason this feature was removed from SSMS in SQL Server 2008. The only locking information provided by the SSMS Activity Monitor in SQL Server 2008 is the indication of lock blocking and wait time provided by the Process Monitor and the Lock Waits information provided by the Resource Waits Monitor. There is no GUI-based lock monitoring tool provided .
