November 14, 2002 at 8:10 pm
We have a server on Win Standard 2000. we are getting deadlocks, I have activated the flag 1204, to collect the dead lock details. When the dead lock occurs, I don't see the dead lock data either in the server error log or on the application error log or on the console(terminal), where is it sending that information. Could some one help me?
venkat
venkat
November 15, 2002 at 4:19 am
The simplest solution is by looking in the enterprise manager.
In the map current activity\locks you can see the current locks. It shows if a lock is blocked and by what process. You see the deadlock immediately. Double clicking on the lock icon gives you the statement.
Joachim.
November 15, 2002 at 5:22 am
I changed my mind. You have to be looking at the enterprise manager at the time of the deadlock to let that work.
Use the profiler and select SQL:BatchStarting,Lock:Deadlock and Lock:Deadlock Chain.
Be sure to add the data columns: TextData, DatabaseId, ObjectID and IndexID to get the necessary information
Joachim.
November 15, 2002 at 7:10 am
Alternately, you can also query sysprocesses table and it will give you the blocking resource. Enterprise Manager is a good tool but you have to refresh on your own and this mis-leads to the information provided.
November 15, 2002 at 7:28 am
Here is a simple proc that we wrote to watch blocking in the server...we just called it "x" so it's quick to type:
CREATE PROC x
@loginame VARCHAR(30) = '%'
AS
DECLARE
@low INTEGER,
@high INTEGER,
@spidlow INTEGER,
@spidhigh INTEGER,
@ExecStr VARCHAR(50),
@CurrSpidINTEGER,
@CurrBufferNVARCHAR(255)
SET NOCOUNT ON
SELECT
@low = 0,
@high = 32767,
@spidlow = 0,
@spidhigh = 32767
SELECT
spid,
status =SUBSTRING(status,1,8),
loginame =SUBSTRING(loginame,CHARINDEX('\',loginame)+1,15),
hostname = SUBSTRING(hostname,1,20),
blk = CONVERT(CHAR(3),blocked),
command =SUBSTRING(cmd,1,14),
cpu=STR(cpu,12),
[io] =STR(physical_io,12),
host =SUBSTRING(hostprocess,1,5),
buffer = CONVERT(VARCHAR(255),NULL)
INTO #work1
FROM master..sysprocesses
WHERE loginame like '%'+@loginame+'%'
SELECT blk
INTO #work2
FROM #work1
WHERE blk != '0'
-- Find all the last processes
CREATE TABLE #inputbuffer
(
EventType NVARCHAR(30),
ParametersINT,
EventInfoNVARCHAR(255)
)
WHILE EXISTS (SELECT * FROM #work1 WHERE buffer IS NULL AND Host != '')
BEGIN
SELECT TOP 1 @CurrSpid = spid FROM #work1 WHERE buffer IS NULL AND HOST != ''
SET @ExecStr = 'DBCC INPUTBUFFER('+ STR(@CurrSpid) +') WITH NO_INFOMSGS'
INSERT #inputbuffer EXEC (@ExecStr)
SELECT TOP 1 @CurrBuffer = EventInfo FROM #inputbuffer
UPDATE #work1SET buffer = ISNULL(@CurrBuffer,'') WHERE spid = @CurrSpid
DELETE #inputbuffer-- clear table for next spid
END
IF EXISTS (SELECT * FROM #work2)
BEGIN
PRINT 'BLOCKED PROCESSES................'
SELECT * FROM #work1 w1 WHERE w1.blk != '0'
PRINT 'BLOCKING PROCESSES...............'
SELECT DISTINCT w1.* FROM #work1 w1, #work2 w2
WHERE w1.spid = convert(INT,w2.blk)
END
ELSE
PRINT 'NO PROCESSES ARE BEING BLOCKED.'
RETURN (0)
GO
-Dan
-Dan
November 15, 2002 at 7:44 am
Microsoft has KB articles on how to monitor blocking in SQL Server 7 and 2000. Basically, they work similar to the stored procedure given. What MS recommends is a process that runs every 5 minutes (stays connected to the whole time) and dumps the results to a text file if it sees blocking occuring. The blocking file can grow pretty large if a lot of it is occuring.
Unfortunately, the KB articles don't seem to be available right now. MS is undergoing a revision to their KB system and that's probably the cause. The article numbers should be: 271509 (SQL 2000) and 251004 (SQL 7).
I have stored as HTML e-mail messages for the scenario where I couldn't get to the web and needed 'em. If you email me off-line, I'll forward both your way.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 15, 2002 at 8:36 am
You have alos to turn on trace flag 3605 for the locking information be logged in the SQL Server errorlog.
November 18, 2002 at 12:00 am
trace flag 3605 (log tarcedata) should be combind with 1204 (explain deadlocks).
Do it at server level as startup parameters. Otherwise it latst only for the duration of that session.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 18, 2002 at 7:14 am
Unfortunately, I was on the phone with MS Support Friday hunting this. The actual syntax per MS is:
DBCC TRACEON(1204,3605,-1)
1204 = Trace Deadlocks
3605 = Write to Server Log file
-1 = All connections
PlanetJam Media Group
PlanetJam Media Group
November 25, 2002 at 10:27 am
If all the below said trace flags are on you can see the detailed deadlock information in the SQL Server Error Log File
DBCC TRACEON(1204)
DBCC TRACEON(1205)
DBCC TRACEON(3604)
DBCC TRACEON(3605)
But these traces consume much of the servers resource they should not remain on for a long period otherwise it will slowdown server processes
Bakthi Margan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply