Set trace flags at the command prompt(Urgent)

Hello,

I would like to get the complete information of deadlocks, Can anyone guide me how do i run trace flags (-1, 1204, 3605) from command prompt.

Thanks
Sejal

Use OSQL.EXE to run dbcc

osql.exe /e /Syourserver /Q"dbcc traceon (-1,1204)"

Is there a way to start server using sqlservr with a trace flag (-1, 1204, 3605) I tried but it hangs, any help is appreciated.

Is there a way to start server using sqlservr with a trace flag (-1, 1204, 3605) I tried but it hangs, any help is appreciated.

Change the starup parameters in Enterprise Manager, server properties. You have to use -T1204, you don't need -1.

Thanks A Lot.
I'm having lot of deadlocking occurring in my server, when I do DBCC Traceon(-1,1204,3605). I don't get the commands which cause deadlock, instead I get
(Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..) Would this startup parameter gives me detailed information or Is there a way I can capture detailed information. I would appreciate your help

Start sql server with 3605,1204,1205 trace flags.

Should I startserver with setting the Startup Parameters -T1204,3605,1205? Please guide me, I'm running MS SQL Server 2000 with SP2 and has clustering enviornment.

Thanks
Sejal

It is same whether you set the traceflag using dbcc -1,xx or use startup parameter.

Flag 3605 logs all the details in sql server error log. Did you check error log?

I didn't see any info as there is no deadlock, yesterday I restarted the server with -T1204 as startup parameter, before I had rum DBCC traceflagon(-1, 1204,3605) but couldn't get full info in sql log only that the deadlock has occurred, I want the command thats causing deadlocks.

Should I change my startup parameters to -T(1204,3605)?

Thanks A lot

When you run

dbcc tracestatus(-1)

do you see all trace flags you set to ON.

Change startup to

-T1204
-T1205
-T3605

I still don't see the commands and full description, Am I missing something

The sql errorlog reads
Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

Thanks In Advance

Sejal

Try to create deadlock using two connections in query analyzer, in one run

begin tran
update titles set title=''
waitfor delay '00:00:05'
update authors set address='a'

and in the second one run

begin tran
update authors set address = ''

update titles set title=''

One of them will deadlock, make sure you rollback the other one.

I just tried it on my server with trace flags 1204, 1205 and 3605 turned on using

dbcc traceon(-1,1204)
dbcc traceon(-1,3605)
dbcc traceon(-1,1205)

Thanks A lot.

-Sejal