Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

The easy way to find deadlocks

A quick, simple and “easy” way to find deadlocks I’ve found is to do the following:

  1. Turn on the trace flag 1222
  2. Wait for a deadlock to happen
  3. Open query analyzer and run the following script:
Create Table #ErrorLogTable
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
SET NOCOUNT ON
INSERT INTO #ErrorLogTable
EXEC xp_readerrorlog
Select
*
-- , Left(right(loginfo, Len(loginfo) - 20), charindex(' ',(right(loginfo, Len(loginfo) - 20))))
from #ErrorLogTable
where Logdate > getdate() - 1 --'2/11/09 12:00 AM'
and ProcessInfo not in ('Backup', 'Logon')
and loginfo not like 'This instance of SQL Server has been using a process ID %'
--and loginfo like '%procname%'
--and LogDate > '2010-05-06 11:25:15.780'
order by Logdate

--Drop table #ErrorLogTable

To Get the Objects involved:

  1. Uncomment the line “and loginfo like ‘%procname%’”
  2. Uncomment the line ”Left(right(loginfo, Len(loginfo) – 20), charindex(‘ ‘,(right(loginfo, Len(loginfo) – 20))))”
  3. Use the Logdate to limit your results.
May 6th, 2010 | Tags: , , | Category: Scripts | One comment

Get Fairly Accurate Table Counts

Here’s a simple script that I use often – a quick hitter to find table row counts, that doesn’t use the Select Count(*). It uses the sysindexes table thus the reason it’s called “fairly accurate”.

SELECT
 [TableName] = so.name,
 [RowCount] = MAX(si.rows)
FROM
 sysobjects so,
 sysindexes si
WHERE
 so.xtype = 'U'
 AND
 si.id = OBJECT_ID(so.name)
GROUP BY
 so.name
ORDER BY
 2 DESC
January 6th, 2010 | Tags: , , | Category: Scripts | Leave a comment

Table Size

--This will give you the amount of disk space that each table uses in a database.

SET NOCOUNT ON
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit

CREATE TABLE #TempTable
 ( [Table_Name] varchar(50),
 Row_Count int,
 Table_Size varchar(50),
 Data_Space_Used varchar(50),
 Index_Space_Used varchar(50),
 Unused_Space varchar(50)
 )

SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
INSERT INTO #TempTable EXEC(@cmdstr)

--Use one of these depending what you are looking for.
--SELECT * FROM #TempTable ORDER BY Table_Name
SELECT * FROM #TempTable ORDER BY Len(Table_Size) DESC, Table_Size DESC

--DROP TABLE #TempTable

December 30th, 2009 | Tags: , , | Category: Scripts | Leave a comment