what is "suspended" status of processes in sql server 2005?

Question

what is "suspended" status of processes in sql server 2005?

lokhande.deepali

SSC Eights!

Points: 833
More actions
September 11, 2008 at 5:34 am

#125398

Once a user process is suspended, it stays suspended until the front-end Access database is close.
A suspended process blocks another process, which in my case the front-end Access cannot insert/update/delete a record.
In my server some system processes are also maked as suspended
form longer time.
Database:Sql Server 2005 Enterprise edition X64(sp2)
O.S :windows 2003 Enterprise edition 64bit(sp1)
Could someone explain to me what is "Suspended", how to deal with it, or maybe more important, how to avoid it?
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

MSSQL marks a process as "suspended" when the process has made a request to a non-SQL process or resource and is awaiting a response. This happens a lot when you have slow disk drives, processes will be suspended while MSSQL waits for the drive to return data or report back after committing.

To deal with long running processes that get suspended, you need to examine the process and the system to determine what resource the process is waiting for. It could be processor time, disks, etc. Then you have to fix the bottleneck.

lokhande.deepali SSC Eights! Points: 833 More actions · Answer 2

Thanks a lot, Michael.

After executing sp_who2,

one of my system process shows status like this,

spid:9 status:SUSPENDED sa master CHECKPOINT 38122568

I really don't understand,why this process is in suspended status from

log time.Can you please help me how to slove this problem.

Thanks again

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Is there an entry in the 'blocked by' column? Is there a last wait type?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

I recommend a great article that among other things explains the statuses that each SPID can have. Don’t be alarmed by the article’s size. Most of it is a big table that the beginning of the article how to use. The article can be found at http://www.davewentzel.com/Document%20Library/Performance_Tuning_Waits_Queues.doc.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/