Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
venkath
Posting Yak Master
202 Posts |
 Posted - 2008-04-01 : 06:24:34
|
| Hi AllWhen 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 2005Thanks in advance. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-01 : 06:32:45
|
| 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. |
 |
|
|
venkath
Posting Yak Master
202 Posts |
Posted - 2008-04-01 : 06:38:28
|
| select serverproperty('productlevel'),serverproperty('edition'),serverproperty('productversion')I got the following values for the above query. What is the latest service pack for sql 2005.SP2 Developer Edition (64-bit)9.00.3042.00Please let me knowThanks. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-01 : 06:39:27
|
| 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. |
|
|
|
venkath
Posting Yak Master
202 Posts |
Posted - 2008-04-01 : 07:12:16
|
| 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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-01 : 23:28:02
|
| SUSPENDED means the process is waiting for resource like disk i/o. |
|
|
|
venkath
Posting Yak Master
202 Posts |
Posted - 2008-04-02 : 09:27:51
|
| 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/OCan you please suggest whether above configuration is causing dead locks and if yes can you please suggest the alternatives.Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-02 : 23:07:20
|
| Better to put log file on its own raid 1 disk. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-03 : 06:39:01
|
| 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