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.
That lokos ok. Are the suspended processes causing 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 experiencing Dead locks on the datbase frequently. but i would like to know why it displays process status as "SUSPENDED". I believe suspended processes will also increase tempdb size.
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.
Did you write everything in-house? If so you should review what was done - it sounds like it's not implemented to avoid deadlocks and blocking.
========================================== 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.
SUSPENDED processes
Posted - 04/01/2008 : 06:24:34
