deAttach database: traceon

Showing posts with label traceon. Show all posts

Sunday, March 25, 2012

Deadlock: Trace flag 1205, 1204

I want to log deadlocks. In query analyzer I ran dbcc
traceon(1205, 1204) on two different SQL Server 2000, SP3a
boxes. Stopped SQL Services and restarted on each box.
Created deadlocks on both boxes via the problem
application. Deadlocks are being written to sql server
logs on one box but not the other.
What is the difference and how can I tell if 1205 and 1204
trace flags are active?dbcc tracestatus(-1)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mike Mullane" <mike.mullane@.hpinc.com> wrote in message
news:082101c38915$62471510$a301280a@.phx.gbl...
> I want to log deadlocks. In query analyzer I ran dbcc
> traceon(1205, 1204) on two different SQL Server 2000, SP3a
> boxes. Stopped SQL Services and restarted on each box.
> Created deadlocks on both boxes via the problem
> application. Deadlocks are being written to sql server
> logs on one box but not the other.
> What is the difference and how can I tell if 1205 and 1204
> trace flags are active?
>|||Hi Mike,
Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays the status of all
currently enabled trace flags by specifying a value of -1.
Please make sure that you problem application can make deadlock every time
when you execute it. Here is a deadlock example, please to perform the on
both SQL Server using Query Analyzer and check to see if the deadlock is
recorded in both SQL Server's log.
Create a simple deadlock in pubs in two Query Analyzer windows.
Window 1:
dbcc traceon(3605)
dbcc traceon(1204)
begin tran update authors set contract = contract
Window 2: begin tran update titles set ytd_sales = ytd_sales
Window 1: update titles set ytd_sales = ytd_sales
Window 2: update authors set contract = contract
It works on my side and I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello Michael,
Perfect advice. I am able to recreate locks using your
example and validate the they are being written to the
log. However, I'm was having issues getting DBCC
TRACESTATUS(-1) or DBCC TRACESTATUS(1204) to behave as
described. When I run it I get: "Trace option(s) not
enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator."
So I ran "DBCC TRACEON" and then aftter running that I
ran "DBCC TRACESTATUS(-1)" and I get "TraceFlag Status
-- --
1204 1" which is what I want. So, it seems that the
order needed is "DBCC TRACEON(1204)" then "DBCC TRACEON"
must be run before "DBCC TRACESTATUS(-1)" will list.
Thanks for your help. I've learned a bit.
Mike
>--Original Message--
>Hi Mike,
>Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays
the status of all
>currently enabled trace flags by specifying a value of -1.
>Please make sure that you problem application can make
deadlock every time
>when you execute it. Here is a deadlock example, please
to perform the on
>both SQL Server using Query Analyzer and check to see if
the deadlock is
>recorded in both SQL Server's log.
>Create a simple deadlock in pubs in two Query Analyzer
windows.
>Window 1:
>dbcc traceon(3605)
>dbcc traceon(1204)
>begin tran update authors set contract = contract
>Window 2: begin tran update titles set ytd_sales =ytd_sales
>Window 1: update titles set ytd_sales = ytd_sales
>Window 2: update authors set contract = contract
>It works on my side and I am standing by for your
response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>

DeadLock Trace

We set our Deadlock Trace on with the following command:

DBCC TRACEOn (1204, -1)
DBCC TRACEOFF (3605, -1);
DBCC TRACESTATUS (-1)
GO

In 2000 this worked fine but in 2005 we get the following error:

Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:

What do I need to do to fix this so we can see the cause of the deadlock in the SQL Server logs?

The statements above do work on SQL2005 and will output deadlock graphs to the errorlog.

There is a new trace flag in SQL2005 that enhances the information output on deadlock: trace flag 1222.

What log viewer are you using to view the errorlogs? Can you open the errorlog files with notepad instead?

|||Thanks for the information. I use the SQL Server Log viewer in Microsoft SQL Server Management Studio to view the logs.|||

Hi Guys,

Unlike SQL 2000, Trace flag 3605 does not work in SQL Server 2005.

regards

Jag