A simple script created to summarize locking by spid, by database, by type with more user friendly descriptions. You can also run for a specific spid. Hope it's useful.
create procedure sp_Spec_CheckLocks
@spid smallint = NULL
as
/** Created by AugieBros to monitor active locking for
a given instance.
This is a simple query designed to summarize locking
for a single spid, or for all spids. Hope it's useful.
**/
IF @spid IS NULL
BEGIN
select req_spid,
convert(varchar(30),b.name) + ' (' + convert(char(2),rsc_dbid) + ') ' as DBName_ID,
convert(char(2), rsc_type) +
CASE rsc_type
WHEN 1 THEN 'NULL Resource'
WHEN 2 THEN 'Database'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Index'
WHEN 5 THEN 'Table'
WHEN 6 THEN 'Page'
WHEN 7 THEN 'Key'
WHEN 8 THEN 'Extent'
WHEN 9 THEN 'RID'
ELSE 'Unlisted'
END as LockType,
CASE
WHEN req_status = 1 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Granted'
WHEN req_status = 1 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Granted'
WHEN req_status = 1 and req_mode = 3 THEN 'IS (Intent Shared) _ Granted'
WHEN req_status = 1 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Granted'
WHEN req_status = 1 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 8 THEN 'S (Shared) _ Granted'
WHEN req_status = 1 and req_mode = 9 THEN 'U (Update) _ Granted'
WHEN req_status = 1 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Granted'
WHEN req_status = 1 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 12 THEN 'IU (Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Granted'
WHEN req_status = 1 and req_mode = 14 THEN 'X (Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 15 THEN 'BU (bulk operations) _ Granted'
WHEN req_status = 2 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Converting'
WHEN req_status = 2 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Converting'
WHEN req_status = 2 and req_mode = 3 THEN 'IS (Intent Shared) _ Converting'
WHEN req_status = 2 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Converting'
WHEN req_status = 2 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 8 THEN 'S (Shared) _ Converting'
WHEN req_status = 2 and req_mode = 9 THEN 'U (Update) _ Converting'
WHEN req_status = 2 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Converting'
WHEN req_status = 2 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 12 THEN 'IU (Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Converting'
WHEN req_status = 2 and req_mode = 14 THEN 'X (Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 15 THEN 'BU (bulk operations) _ Converting'
WHEN req_status = 3 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Waiting'
WHEN req_status = 3 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Waiting'
WHEN req_status = 3 and req_mode = 3 THEN 'IS (Intent Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 8 THEN 'S (Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 9 THEN 'U (Update) _ Waiting'
WHEN req_status = 3 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Waiting'
WHEN req_status = 3 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 12 THEN 'IU (Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Waiting'
WHEN req_status = 3 and req_mode = 14 THEN 'X (Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 15 THEN 'BU (bulk operations) _ Waiting'
ELSE 'Unclassified'
END as LockMode,
count(*) as LockCount
from master..syslockinfo a LEFT JOIN master..sysdatabases b ON a.rsc_dbid = b.dbid
group by req_spid,
rsc_dbid,
b.name,
convert(char(2), rsc_type) +
CASE rsc_type
WHEN 1 THEN 'NULL Resource'
WHEN 2 THEN 'Database'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Index'
WHEN 5 THEN 'Table'
WHEN 6 THEN 'Page'
WHEN 7 THEN 'Key'
WHEN 8 THEN 'Extent'
WHEN 9 THEN 'RID'
ELSE 'Unlisted'
END,
CASE
WHEN req_status = 1 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Granted'
WHEN req_status = 1 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Granted'
WHEN req_status = 1 and req_mode = 3 THEN 'IS (Intent Shared) _ Granted'
WHEN req_status = 1 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Granted'
WHEN req_status = 1 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 8 THEN 'S (Shared) _ Granted'
WHEN req_status = 1 and req_mode = 9 THEN 'U (Update) _ Granted'
WHEN req_status = 1 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Granted'
WHEN req_status = 1 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 12 THEN 'IU (Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Granted'
WHEN req_status = 1 and req_mode = 14 THEN 'X (Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 15 THEN 'BU (bulk operations) _ Granted'
WHEN req_status = 2 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Converting'
WHEN req_status = 2 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Converting'
WHEN req_status = 2 and req_mode = 3 THEN 'IS (Intent Shared) _ Converting'
WHEN req_status = 2 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Converting'
WHEN req_status = 2 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 8 THEN 'S (Shared) _ Converting'
WHEN req_status = 2 and req_mode = 9 THEN 'U (Update) _ Converting'
WHEN req_status = 2 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Converting'
WHEN req_status = 2 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 12 THEN 'IU (Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Converting'
WHEN req_status = 2 and req_mode = 14 THEN 'X (Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 15 THEN 'BU (bulk operations) _ Converting'
WHEN req_status = 3 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Waiting'
WHEN req_status = 3 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Waiting'
WHEN req_status = 3 and req_mode = 3 THEN 'IS (Intent Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 8 THEN 'S (Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 9 THEN 'U (Update) _ Waiting'
WHEN req_status = 3 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Waiting'
WHEN req_status = 3 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 12 THEN 'IU (Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Waiting'
WHEN req_status = 3 and req_mode = 14 THEN 'X (Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 15 THEN 'BU (bulk operations) _ Waiting'
ELSE 'Unclassified'
END
order by req_spid, DBName_ID, LockType desc
END
ELSE
BEGIN
select convert(varchar(30),b.name) + ' (' + convert(char(2),rsc_dbid) + ') ' as DBName_ID,
convert(char(2), rsc_type) +
CASE rsc_type
WHEN 1 THEN 'NULL Resource'
WHEN 2 THEN 'Database'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Index'
WHEN 5 THEN 'Table'
WHEN 6 THEN 'Page'
WHEN 7 THEN 'Key'
WHEN 8 THEN 'Extent'
WHEN 9 THEN 'RID'
ELSE 'Unlisted'
END as LockType,
CASE
WHEN req_status = 1 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Granted'
WHEN req_status = 1 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Granted'
WHEN req_status = 1 and req_mode = 3 THEN 'IS (Intent Shared) _ Granted'
WHEN req_status = 1 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Granted'
WHEN req_status = 1 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 8 THEN 'S (Shared) _ Granted'
WHEN req_status = 1 and req_mode = 9 THEN 'U (Update) _ Granted'
WHEN req_status = 1 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Granted'
WHEN req_status = 1 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 12 THEN 'IU (Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Granted'
WHEN req_status = 1 and req_mode = 14 THEN 'X (Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 15 THEN 'BU (bulk operations) _ Granted'
WHEN req_status = 2 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Converting'
WHEN req_status = 2 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Converting'
WHEN req_status = 2 and req_mode = 3 THEN 'IS (Intent Shared) _ Converting'
WHEN req_status = 2 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Converting'
WHEN req_status = 2 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 8 THEN 'S (Shared) _ Converting'
WHEN req_status = 2 and req_mode = 9 THEN 'U (Update) _ Converting'
WHEN req_status = 2 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Converting'
WHEN req_status = 2 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 12 THEN 'IU (Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Converting'
WHEN req_status = 2 and req_mode = 14 THEN 'X (Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 15 THEN 'BU (bulk operations) _ Converting'
WHEN req_status = 3 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Waiting'
WHEN req_status = 3 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Waiting'
WHEN req_status = 3 and req_mode = 3 THEN 'IS (Intent Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 8 THEN 'S (Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 9 THEN 'U (Update) _ Waiting'
WHEN req_status = 3 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Waiting'
WHEN req_status = 3 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 12 THEN 'IU (Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Waiting'
WHEN req_status = 3 and req_mode = 14 THEN 'X (Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 15 THEN 'BU (bulk operations) _ Waiting'
ELSE 'Unclassified'
END as LockMode,
count(*) as LockCount
from master..syslockinfo a LEFT JOIN master..sysdatabases b ON a.rsc_dbid = b.dbid
where req_spid = @spid
group by rsc_dbid,
b.name,
convert(char(2), rsc_type) +
CASE rsc_type
WHEN 1 THEN 'NULL Resource'
WHEN 2 THEN 'Database'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Index'
WHEN 5 THEN 'Table'
WHEN 6 THEN 'Page'
WHEN 7 THEN 'Key'
WHEN 8 THEN 'Extent'
WHEN 9 THEN 'RID'
ELSE 'Unlisted'
END,
CASE
WHEN req_status = 1 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Granted'
WHEN req_status = 1 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Granted'
WHEN req_status = 1 and req_mode = 3 THEN 'IS (Intent Shared) _ Granted'
WHEN req_status = 1 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Granted'
WHEN req_status = 1 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 8 THEN 'S (Shared) _ Granted'
WHEN req_status = 1 and req_mode = 9 THEN 'U (Update) _ Granted'
WHEN req_status = 1 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Granted'
WHEN req_status = 1 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 12 THEN 'IU (Intent Update) _ Granted'
WHEN req_status = 1 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Granted'
WHEN req_status = 1 and req_mode = 14 THEN 'X (Exclusive) _ Granted'
WHEN req_status = 1 and req_mode = 15 THEN 'BU (bulk operations) _ Granted'
WHEN req_status = 2 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Converting'
WHEN req_status = 2 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Converting'
WHEN req_status = 2 and req_mode = 3 THEN 'IS (Intent Shared) _ Converting'
WHEN req_status = 2 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Converting'
WHEN req_status = 2 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 8 THEN 'S (Shared) _ Converting'
WHEN req_status = 2 and req_mode = 9 THEN 'U (Update) _ Converting'
WHEN req_status = 2 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Converting'
WHEN req_status = 2 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 12 THEN 'IU (Intent Update) _ Converting'
WHEN req_status = 2 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Converting'
WHEN req_status = 2 and req_mode = 14 THEN 'X (Exclusive) _ Converting'
WHEN req_status = 2 and req_mode = 15 THEN 'BU (bulk operations) _ Converting'
WHEN req_status = 3 and req_mode = 1 THEN 'Sch-S (Schema stability) _ Waiting'
WHEN req_status = 3 and req_mode = 2 THEN 'Sch-M (Schema modification) _ Waiting'
WHEN req_status = 3 and req_mode = 3 THEN 'IS (Intent Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 4 THEN 'SIU (Shared Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 5 THEN 'IS-S (Intent Shared-Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 6 THEN 'IX (Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 7 THEN 'SIX (Shared Intent Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 8 THEN 'S (Shared) _ Waiting'
WHEN req_status = 3 and req_mode = 9 THEN 'U (Update) _ Waiting'
WHEN req_status = 3 and req_mode = 10 THEN 'IIn-Nul. (Intent Insert-NULL) _ Waiting'
WHEN req_status = 3 and req_mode = 11 THEN 'IS-X (Intent Shared-Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 12 THEN 'IU (Intent Update) _ Waiting'
WHEN req_status = 3 and req_mode = 13 THEN 'IS-U (Intent Shared-Update) _ Waiting'
WHEN req_status = 3 and req_mode = 14 THEN 'X (Exclusive) _ Waiting'
WHEN req_status = 3 and req_mode = 15 THEN 'BU (bulk operations) _ Waiting'
ELSE 'Unclassified'
END
order by DBName_ID, LockType desc
END
GO 
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,794 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads
Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.
2008-08-13
6,696 reads
This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.
2001-10-28
1,578 reads
Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]
2001-10-28
1,200 reads