backup hanging

sunbaked

Hi,

Sorry, i think i just added this to a wrong post...

My SQL Backup 6.2.0.134 is hanging on different databases during the backup process - the hanging continues until noticed and the only way around it is to restart the sql server - at which time it starts back on the next database that in the process (even though its 4-5 days later).

Thinking maybe something was corrupt i have taken to restarting sql and deleting the database it hung on... as of today i am 6 databases down

Any suggestions? I never had the issue on this (or any other) server with v5 but after a client upgraded we have begun upgrading all of our internal development/test servers. We are halted on that process until i can figure out the issue here. Any help would be appreciated... thanks!

Brian Donahue

Hi Brock,

Red Gate had recieved a lot of reports about backups hanging and at one point I had grouped them all into an issue (group of incidents) so that we could see if they had anything in common. That issue had been closed down a few weeks ago because I had only found one problem and we produced a patch for it.

The problem only seems to happen when the disk runs out of space during the backup process, and in that case the server SPID is kept in an indefinite "wait" state. This patch is available from

ftp://support.red-gate.com/patches/SQL_ ... 0_1001.zip

In all of the other cases, the SQL Backup job was trying to copy files. I think that the introduction of "network resiliency" has caused a lot of accusations about hanging that are due to COPYTO jobs retrying. A description of how the copy queue works can be found here:

http://www.red-gate.com/supportcenter/C ... aspx?p=SQL Backup&c=SQL_Backup%5chelp%5c6.2%5cSBU_Log_Copy_Queue.htm

If you can't isolate the cause to either of these, by all means create a stack dump and send it in to support [at] red-gate.com:

• Connect to the SQL Server with your query tool (SSMS)
• exec master..sqbutility 9997
• Get this file from the SQL Server: SQBCoreService_bugreport.txt. It will be created in the SQL Backup server components program folder (%programfiles%\red gate\sql backup 6\(local))

sunbaked

Hi Brian,

Thank you for the reply.

First, a little more detail... the server is running under vmsphere and has 100GBs allocated to it. There is still 89GBs available locally and 300GBS+ available on the server it was copying to, so i don't think hard drive space is the issue. I have also shutoff the network copy in the backup job to eliminate that - i will let you know. I have shutoff the verify and anything else that seemed to run post backup.

When i remoted in to do all of this, it had been stuck on the same backup job the past 40hours - roughly mid way through the list. When i go back in after restarting it starts with the next file in the list, each subsequent file failing

When i look at the physical backup file it says 0, when i restart the sql server the file now says 6MBs (roughly the size it should be) and the process continues on... failing on every subsequent file.

The problem seems easily reproducable on our server and tends to happen in a grouping of different databases that all have the same logical name... if you would like some help trying to reproduce the issue to resolve it, this may be a good opportunity... at least i always appreciate it when a customer of ours has an easily reproducable error!!

Let me know, otherwise keep me informed of anything else you come up with as we are still suffering and at this point, all the money i spent on upgrades is definitely not paying for itself!!!!!

Brian Donahue

I'd say definitely try the patch. If you have a file size of 0, I'd assume from the description in the release notes that the backup would at the very least not "hang" any more.

If you see long running jobs, like I said, feel free to use SQBUTILITY 9997 and send the stack dump in to us.

sunbaked

Hi Brian,

Well, it looked promising but it didn't last. Same issue.

Now the backup is going, i have the zero sized backup file... do i run the SQBUTILITY 9997 now, do i terminate first??

When i run that, while the backup is still trying, i do not get the bugreport file?? Help

Brian Donahue

sqbutility 9997 should always create a bug report, provided the SQL Backup Agent Service is running and answering requests.

sunbaked

Hi Brian,

Is there a way to email or post a screenshot? I have followed through your instructions but it still hangs and still doesn't produce a dump file... i think the screenshot shows all of that quite nicely

Brian Donahue

You can email screenshots to support@red-gate.com, but what we really want to know is what SQL Backup is doing or THINKS it's doing internally.

sunbaked

Hi Brian,

I just emailed off the screenshot - i am hoping it may help highlight what i am doing wrong to get you what you need.