Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SUSPENDED processes		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

venkath
Posting Yak Master

India
202 Posts
Posted - 04/01/2008 :  06:24:34  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts
Posted - 04/01/2008 :  06:32:45  Show Profile  Visit nr's Homepage  Reply with Quote
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

India
202 Posts
Posted - 04/01/2008 :  06:38:28  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts
Posted - 04/01/2008 :  06:39:27  Show Profile  Visit nr's Homepage  Reply with Quote
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

India
202 Posts
Posted - 04/01/2008 :  07:12:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts
Posted - 04/01/2008 :  23:28:02  Show Profile  Reply with Quote
SUSPENDED means the process is waiting for resource like disk i/o.
Go to Top of Page

venkath
Posting Yak Master

India
202 Posts
Posted - 04/02/2008 :  09:27:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts
Posted - 04/02/2008 :  23:07:20  Show Profile  Reply with Quote
Better to put log file on its own raid 1 disk.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 04/03/2008 :  06:39:01  Show Profile  Visit nr's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000