When i see the result of Sp_who2, i see the status of a process as "SUSPENDED" for some of the processes like logwriter, checkpoint etc. and also found the same status for "IF @@TRANCOUNT>0 COMMIT TRAN"
Please let me know what does it mean in the context of SQL 2005
Do you have the latest service pack? There have been a number of fixes for parallelism problems which can end up with suspended processes. It usually means that the process is waiting for something to happen so may also be a valid state. For the logwriter and checkpoint it's probably waiting to be kicked off again so not a problem.
========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.
we are exeperiencing frequent dead locks on the databases . Each database has 9 file groups in it and all data and log files are placed in D drive (RAID 5). Is there any wrong with the above configuration? Most of the time i see the SUSPENDED status for the commands "IF @@TRANCOUNT>0 COMMIT TRAN". which means waiting for I/O
Can you please suggest whether above configuration is causing dead locks and if yes can you please suggest the alternatives.