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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SUSPENDED processes

Author  Topic 

venkath
Posting Yak Master

202 Posts

Posted - 2008-04-01 : 06:24:34
Hi All

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

Thanks 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.
Go to Top of Page

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.00

Please let me know

Thanks.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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/O

Can you please suggest whether above configuration is causing dead locks and if yes can you please suggest the alternatives.

Thanks
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -