Running those trace flags (as startup parameters), will not impact your users or your application. This will print deadlock information in the sql server error log. This information will be very valuable for you in troubleshooting the deadlocks.

Deadlocks are very difficult to sort out, I am grappling with some of my own right at the moment.

Useful links:-

http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx

thanks for the help. So turning the trace files on will not have any impact on performance.

I read some where that it may slow down database.

There is some overhead, but it is minimal.  Do not monitor for every possible event, be selective in what you monitor.

Hi,

Today our production server was hanged cos.....the number of current users was increasing and it was around 300+, but in normal case the number of users are around 30-35, I open the server through VNC and then tried to connect it through enterpirse manager and query analyzer but it gave network error it was something like

"Microsoft SQL server network error : check your network document"

I was physically working on the production server. I checked the services all were running fine I tried it for 10 mins but when I failed. I restarted both the Sql server and Sql server agent services and after that everything was fine and when I connected through enterprise manager their were around 10 process in the current activity that were showing as blocked by some other process, I manually killed those processes and everything was fine but my question is like how can I know the reason for this, so that I can take preventive measures to avoid it in future. This thing happened last months also and it people restarted the server and everything was fine.

Can anybody help in in this regard?

Thank You,

Rohit Paliwal

Please post this as a separate topic.

 Sounds like the SQL server was not listening on the expected ports. One thing you can try in this situation is to telnet to <sqlserver> port 1433 (or whatever port is configured in SQL server) and see if you can make a successful connection and work you way up such as telnet from within your VNC connection to port 1433 etc..

Dilan

Have you tried by tracing on the flags with numbers 1204 and 3605.

 dbcc traceon(1204,1)

dbcc traceon(3605,1)

After placing the trace, restart the server and check for error log to idenctify the server process id to kill it.

Dont kill all the resources or process. Instead, killing specific process may resolve your problem.

Regards,

Venkatesan Prabu .J

See this WIKI http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server from MS