January 20, 2012 at 7:36 am
All of a sudden yesterday I starting having problems where I could not backup one of several databases.
I look at the SPID and it is in a suspended state.
It does not even create the backup file with zero bites.
Any ideas on what to look for?
For better, quicker answers on T-SQL questions, click on the following...
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/
January 20, 2012 at 7:41 am
What is the wait type of that suspended spid ?
January 20, 2012 at 7:44 am
It means it waiting on something. You need to figure out what it's waiting on.
BTW, doesn't sound good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2012 at 8:08 am
Grant Fritchey (1/20/2012)
It means it waiting on something. You need to figure out what it's waiting on.BTW, doesn't sound good.
Grant,
Thanks. What is weird is I executed the jobs to perform the backups from my machine and it created the backup files with zero bytes.
If I RDC into the Server I get a gray screen.
I had a drive drop off earlier in the week and all kinds of weirdness going on.
Weeks ago I tried executing an OPEN Query and the Statement never completed. I tried killing the process but I went to a suspended state. I eventually had to restart the service after hours.:w00t:
For better, quicker answers on T-SQL questions, click on the following...
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/
January 20, 2012 at 8:13 am
Welsh Corgi (1/20/2012)
Grant Fritchey (1/20/2012)
It means it waiting on something. You need to figure out what it's waiting on.BTW, doesn't sound good.
Grant,
Thanks. What is weird is I executed the jobs to perform the backups from my machine and it created the backup files with zero bytes.
If I RDC into the Server I get a gray screen.
I had a drive drop off earlier in the week and all kinds of weirdness going on.
Weeks ago I tried executing an OPEN Query and the Statement never completed. I tried killing the process but I went to a suspended state. I eventually had to restart the service after hours.:w00t:
Yikes. Hmmm... in this situation, I'd probably restart the server and then run DBCC on the planet. I don't think you're in a good position.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 20, 2012 at 8:20 am
Gray screen on RDC indicate either server is heavily loaded or got hung. In either case it’s bad. If server downtime is acceptable for sometime, a restart might give you reasonable amount of time to troubleshoot on the issue till it get the same load again.
January 20, 2012 at 8:23 am
padhis (1/20/2012)
What is the wait type of that suspended spid ?
waittypewaittimelastwaittypestatuslogin_timelast_batch
0x00622028234ASYNC_IO_COMPLETION suspended 2012-01-20 09:48:32.8002012-01-20 09:48:32.800
0x008615BACKUPBUFFER suspended 2012-01-20 09:48:32.8002012-01-20 09:48:32.800
0x00000BACKUPBUFFER runnable 2012-01-20 09:48:32.8002012-01-20 09:48:32.800
For better, quicker answers on T-SQL questions, click on the following...
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/
January 20, 2012 at 8:37 am
Check the windows event log for any disk or IO-related errors.
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
January 20, 2012 at 12:56 pm
GilaMonster (1/20/2012)
Check the windows event log for any disk or IO-related errors.
Thanks for the hint it was definitely IO related.
I did not find any entries in the Event Log but I tried backing up to another drive and I no longer have an issue.
Thanks!:-)
For better, quicker answers on T-SQL questions, click on the following...
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/
January 20, 2012 at 12:57 pm
Get that drive checked out, diagnosed and if necessary replaced.
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
January 20, 2012 at 1:04 pm
GilaMonster (1/20/2012)
Get that drive checked out, diagnosed and if necessary replaced.
In progress, thanks.
For better, quicker answers on T-SQL questions, click on the following...
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/
January 21, 2012 at 3:43 pm
GilaMonster (1/20/2012)
Get that drive checked out, diagnosed and if necessary replaced.
I was going to rely on someone else to do this but I want this to happen sooner rather than latter.
Do you recommend a specific diagnostic tool?
For better, quicker answers on T-SQL questions, click on the following...
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/
January 22, 2012 at 2:40 am
For SANs there will be vendor-specific tools (or there should be)
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply