SQL Server Script to Check for Blocking Queries

SQL Server logo

Even though SQL Server 2000 is no longer officially supported by Microsoft, quite a few SQL 2K databases still exist in production environments. One that had been giving me trouble for a couple of days was intermittently causing a web app to fail, and the error message indicated that the failure due to either a SQL timeout or an unresponsive server. This problem was not constant, but happened every few minutes.

Several other apps that accessed the same database as the one that was only working some of the time. Each app had its own set of tables, so I figured there must be exclusive locks being set on the tables that the problem app used. To determine the cause of this problem, I had to find out what process was locking the table.

This being SQL 2000, I could not use this script, which works on SQL 2008:

USE master
GO
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

For SQL 2000, try this one:

USE master
GO
EXEC master.dbo.sp_lock
GO
EXEC master.dbo.sp_who2
GO
SELECT req_spid AS 'spid',
DB_NAME(rsc_dbid) AS 'Database',
OBJECT_NAME(rsc_objid) AS 'Name',
rsc_indid AS 'Index',
rsc_text AS 'Description',
ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource'
	WHEN rsc_type = 2 THEN 'Database'
	WHEN rsc_type = 3 THEN 'File'
	WHEN rsc_type = 4 THEN 'Index'
	WHEN rsc_type = 5 THEN 'Table'
	WHEN rsc_type = 6 THEN 'Page'
	WHEN rsc_type = 7 THEN 'Key'
	WHEN rsc_type = 8 THEN 'Extent'
	WHEN rsc_type = 9 THEN 'RID (Row ID)'
	WHEN rsc_type = 10 THEN 'Application'
	ELSE 'Unknown'
END,
Status = CASE WHEN req_status = 1 THEN 'Granted'
	WHEN req_status = 2 THEN 'Converting'
	WHEN req_status = 3 THEN 'Waiting'
	ELSE 'Unknown'
END,
OwnerType = CASE WHEN req_ownertype = 1 THEN 'Transaction'
	WHEN req_ownertype = 2 THEN 'Cursor'
	WHEN req_ownertype = 3 THEN 'Session'
	WHEN req_ownertype = 4 THEN 'ExSession'
	ELSE 'Unknown'
END,
LockRequestMode = CASE WHEN req_mode = 0 THEN 'No access '
	WHEN req_mode = 1 THEN 'Sch-S (Schema stability)'
	WHEN req_mode = 2 THEN 'Sch-M (Schema modification)'
	WHEN req_mode = 3 THEN 'S (Shared)'
	WHEN req_mode = 4 THEN 'U (Update)'
	WHEN req_mode = 5 THEN 'X (Exclusive)'
	WHEN req_mode = 6 THEN 'IS (Intent Shared)'
	WHEN req_mode = 7 THEN 'IU (Intent Update)'
	WHEN req_mode = 8 THEN 'IX (Intent Exclusive)'
	WHEN req_mode = 9 THEN 'SIU (Shared Intent Update)'
	WHEN req_mode = 10 THEN 'SIX (Shared Intent Exclusive)'
	WHEN req_mode = 11 THEN 'UIX (Update Intent Exclusive)'
	WHEN req_mode = 12 THEN 'BU. (Bulk operations)'
	WHEN req_mode = 13 THEN 'RangeS_S'
	WHEN req_mode = 14 THEN 'RangeS_U'
	WHEN req_mode = 15 THEN 'RangeI_N'
	WHEN req_mode = 16 THEN 'RangeI_S'
	WHEN req_mode = 17 THEN 'RangeI_U'
	WHEN req_mode = 18 THEN 'RangeI_X'
	WHEN req_mode = 19 THEN 'RangeX_S'
	WHEN req_mode = 20 THEN 'RangeX_U'
	WHEN req_mode = 21 THEN 'RangeX_X'
	ELSE 'Unknown'
END
FROM master.dbo.syslockinfo
GO
SELECT * FROM master.dbo.sysprocesses

This query generates several tables, and the last table is most helpful. The “blocked” column will show how many exclusive locks are being held by the process denoted by the number in the “spid” column.

If you have DBA access, you can run “DBCC INPUTBUFFER(spid)”, substituting the spid number found above, and the text of the troublemaking query will be returned.

Moving Data from the IMAGE Datatype to VARBINARY(MAX) from a SQL Server 2000 Instance

SQL Server logo

I am in the process of writing scripts to move data from a SQL 2000 database into a different database running on SQL 2008.

I had set up a linked server from the SQL 2k server to begin this process, and about 90% of the data could be moved.  One of the last tables to be migrated was one that held file attachments. I ran into a problem when I realized that the SQL 2k table had an IMAGE column, and the corresponding SQL 2k8 table used a VARBINARY(MAX) column.

The SQL2k database has a table with the following definition:

CREATE TABLE [dbo].[tOldTable](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [DocName] [varchar](300) NOT NULL,
 [DocData] [image] NOT NULL,
 [DocContentType] [varchar](300) NOT NULL,
 [User] [varchar](50) NOT NULL,
 [Date] [datetime] NOT NULL,
 [requestId] [int] NOT NULL,
 [requestTypeId] [int] NOT NULL)

The new table, in the SQL2k8 database, has this structure:

CREATE TABLE [dbo].[tNewTable](
 [SupID] [int] IDENTITY(1,1) NOT NULL,
 [ProjectID] [int] NOT NULL,
 [FileName] [varchar](250) NULL,
 [FileExtension] [varchar](25) NULL,
 [MIMEType] [varchar](250) NULL,
 [FileData] [varbinary](max) NULL,
 [Thumbnail] [varbinary](max) NULL,
 [FileSize] [int] NULL,
 [Width] [int] NULL,
 [Height] [int] NULL,
 [FileModificationTimestamp] [datetime] NULL,
 [CreatedBy] [varchar](100) NULL,
 [CreateTimestamp] [datetime] NULL,
 [Removed] [bit] NULL)

At one point, I was planning on using a CURSOR to load the data from the first table into a table variable and explicitly CAST it as VARBINARY(MAX), but then I re-discovered the fact that the IMAGE datatype cannot be stored in a local variable.  My second problem – even if the first step had worked – is that VARBINARY(MAX) is not available on SQL 2000 (it was introduced in SQL 2005), and would cause an error if run from the SQL 2k server.

After refactoring and improving the code, I had removed the CURSOR, but was still uncertain as to how I could move this data.  I decided to JOIN the SQL 2k table with an existing table variable, and directly INSERT the rows like so:

INSERT INTO [SQL2k8].[NewDB].[dbo].[tNewTable]
 ([ProjectID]
 ,[FileName]
 ,[FileExtension]
 ,[MIMEType]
 ,[FileData]
 ,[FileModificationTimestamp]
 ,[CreatedBy]
 ,[CreateTimestamp]
 ,[Removed])
SELECT tv.[ProjectID] AS [ProjectID]
 ,[DocName] AS [FileName]
 ,RIGHT([DocName],3) AS [FileExtension]
 ,[DocContentType] AS [MIMEType]
 ,[DocData] AS [FileData]
 ,[Date] AS [FileModificationTimestamp]
 ,[User] AS [CreatedBy]
 ,[Date] AS [CreateTimestamp]
 ,0 AS [Removed]
 FROM [dbo].[tOldTable] ot
 JOIN @tTableVariable tv ON tv.[requestID] = ot.[requestId]

This was an implicit conversion from IMAGE to VARBINARY(MAX), but since the insertion took place on the SQL 2008 server and the code never mentions the datatype (which would not have been recognized by the SQL 2k instance), no errors resulted. Problem solved!

Incidentally, the IMAGE datatype has been deprecated, and should be converted to VARBINARY(MAX) wherever possible.