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 in single session

Hi guys,
I need to stimulate a deadlock senerio in sql server.. i know i can do
it by openning two sessions of query analyzer.. is there any way of
creating a deadlock with a sigle session..
thanks to all in advance..This section of the SQL2005 BOL discusses, among other things, two tasks in
the same session causing a deadlock:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2ed3e3e7-5080-4fa3-b79a-585470602bc2.htm
But I have not tried to create such a deadlock myself.
Linchi
"ameen.abdullah@.gmail.com" wrote:
> Hi guys,
> I need to stimulate a deadlock senerio in sql server.. i know i can do
> it by openning two sessions of query analyzer.. is there any way of
> creating a deadlock with a sigle session..
> thanks to all in advance..
>|||Hi
Dan Guzman has written this example
-- session 1
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Col2 int NULL
)
INSERT INTO MyTable VALUES(1, NULL)
INSERT INTO MyTable VALUES(2, NULL)
GO
BEGIN TRAN
UPDATE MyTable SET Col2 = 1 WHERE Col1 = 1
GO
-- session 2
BEGIN TRAN
UPDATE MyTable SET Col2 = 2 WHERE Col1 = 2
GO
-- session 1
UPDATE MyTable SET Col2 = 3 WHERE Col1 = 2
GO
-- session 2
UPDATE MyTable SET Col2 = 4 WHERE Col1 = 1
GO
---
Connection 1: BEGIN TRAN
Connection 2: BEGIN TRAN
Connection 1: UPDATE id_Test 1
Connection 2: UPDATE id_Test 2
Connection 1: UPDATE id_Test 2 (waits for Connection 2 to COMMIT)
Connection 2: UPDATE id_Test 1 (waits for Connection 1 to COMMIT)
<ameen.abdullah@.gmail.com> wrote in message
news:1151513128.507479.134950@.y41g2000cwy.googlegroups.com...
> Hi guys,
> I need to stimulate a deadlock senerio in sql server.. i know i can do
> it by openning two sessions of query analyzer.. is there any way of
> creating a deadlock with a sigle session..
> thanks to all in advance..
>

Deadlock in single session

Hi guys,
I need to stimulate a deadlock senerio in sql server.. i know i can do
it by openning two sessions of query analyzer.. is there any way of
creating a deadlock with a sigle session..
thanks to all in advance..This section of the SQL2005 BOL discusses, among other things, two tasks in
the same session causing a deadlock:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2ed3e3e7-5080-4fa3-b79a-5854
70602bc2.htm
But I have not tried to create such a deadlock myself.
Linchi
"ameen.abdullah@.gmail.com" wrote:

> Hi guys,
> I need to stimulate a deadlock senerio in sql server.. i know i can do
> it by openning two sessions of query analyzer.. is there any way of
> creating a deadlock with a sigle session..
> thanks to all in advance..
>|||This section of the SQL2005 BOL discusses, among other things, two tasks in
the same session causing a deadlock:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2ed3e3e7-5080-4fa3-b79a-5854
70602bc2.htm
But I have not tried to create such a deadlock myself.
Linchi
"ameen.abdullah@.gmail.com" wrote:

> Hi guys,
> I need to stimulate a deadlock senerio in sql server.. i know i can do
> it by openning two sessions of query analyzer.. is there any way of
> creating a deadlock with a sigle session..
> thanks to all in advance..
>|||Hi
Dan Guzman has written this example
-- session 1
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Col2 int NULL
)
INSERT INTO MyTable VALUES(1, NULL)
INSERT INTO MyTable VALUES(2, NULL)
GO
BEGIN TRAN
UPDATE MyTable SET Col2 = 1 WHERE Col1 = 1
GO
-- session 2
BEGIN TRAN
UPDATE MyTable SET Col2 = 2 WHERE Col1 = 2
GO
-- session 1
UPDATE MyTable SET Col2 = 3 WHERE Col1 = 2
GO
-- session 2
UPDATE MyTable SET Col2 = 4 WHERE Col1 = 1
GO
---
Connection 1: BEGIN TRAN
Connection 2: BEGIN TRAN
Connection 1: UPDATE id_Test 1
Connection 2: UPDATE id_Test 2
Connection 1: UPDATE id_Test 2 (waits for Connection 2 to COMMIT)
Connection 2: UPDATE id_Test 1 (waits for Connection 1 to COMMIT)
<ameen.abdullah@.gmail.com> wrote in message
news:1151513128.507479.134950@.y41g2000cwy.googlegroups.com...
> Hi guys,
> I need to stimulate a deadlock senerio in sql server.. i know i can do
> it by openning two sessions of query analyzer.. is there any way of
> creating a deadlock with a sigle session..
> thanks to all in advance..
>|||Hi
Dan Guzman has written this example
-- session 1
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Col2 int NULL
)
INSERT INTO MyTable VALUES(1, NULL)
INSERT INTO MyTable VALUES(2, NULL)
GO
BEGIN TRAN
UPDATE MyTable SET Col2 = 1 WHERE Col1 = 1
GO
-- session 2
BEGIN TRAN
UPDATE MyTable SET Col2 = 2 WHERE Col1 = 2
GO
-- session 1
UPDATE MyTable SET Col2 = 3 WHERE Col1 = 2
GO
-- session 2
UPDATE MyTable SET Col2 = 4 WHERE Col1 = 1
GO
---
Connection 1: BEGIN TRAN
Connection 2: BEGIN TRAN
Connection 1: UPDATE id_Test 1
Connection 2: UPDATE id_Test 2
Connection 1: UPDATE id_Test 2 (waits for Connection 2 to COMMIT)
Connection 2: UPDATE id_Test 1 (waits for Connection 1 to COMMIT)
<ameen.abdullah@.gmail.com> wrote in message
news:1151513128.507479.134950@.y41g2000cwy.googlegroups.com...
> Hi guys,
> I need to stimulate a deadlock senerio in sql server.. i know i can do
> it by openning two sessions of query analyzer.. is there any way of
> creating a deadlock with a sigle session..
> thanks to all in advance..
>

DBs not appearing in query analyer dropdown

A couple of databases on one of our SQL Servers is missing in the db
dropdown in query analyzer.

Does anyone know what I need to update to sync this with the actual
databases?

Thanks,

BurtThe user you are logged in with does not have access to those
databases. There is no "syncing" necessary.

dbo.dtproperties information

Hi All

I understand the dbo.dtproperties tables is a system table, but it appears
under "User Tables" in my Query Analyzer.

Is this normal , or does it signify a problem ? I have never seen it there
before.

In the sysobjects table, the xtype field and type field for dtproperties
table, have been set to 'U', which I thought meant User.

Perhaps I should just set them back to 'S' ?

All sugestions welcomed

Cheers

RayRay Watson (rwatson@.oztell.com) writes:
> I understand the dbo.dtproperties tables is a system table, but it appears
> under "User Tables" in my Query Analyzer.
> Is this normal , or does it signify a problem ? I have never seen it there
> before.
> In the sysobjects table, the xtype field and type field for dtproperties
> table, have been set to 'U', which I thought meant User.
> Perhaps I should just set them back to 'S' ?

No. That's the one table that has type = U, but Objectproperty(id,
'IsMSShipped') = 1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp