Query to verify Excusive Locks in SQL Server from 2005 to 2012

The next code can be modified to check any other kind of lock's, this is usefull when we are gathering information of T-SQL Code or transactions being processed by the server.


SELECT
convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
OBJECT_NAME (rsc_objid) ObjectName,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 32) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
 FROM
master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u

 WHERE   master.dbo.syslockinfo.rsc_type = v.number
   and v.type = 'LR'  
   and master.dbo.syslockinfo.req_status = x.number
   and x.type = 'LS'  
   and master.dbo.syslockinfo.req_mode + 1 = u.number
   and u.type = 'L'
   and rsc_objid >0
   and  substring (u.name, 1, 8) like '%X%'

Some DBCC SQL Server Commands

Reseed an identity column of a table

DBCC CHECKIDENT (TABLE, RESEED, 1)

Shrink the log file of a database

DBCC SHRINKFILE (DB_log, Size)

BACKUP LOG DB 

WITH TRUNCATE_ONLY