Backup process will not die

Question

Post reply

Backup process will not die

Timothy Patihk

Right there with Babe

Points: 793
More actions
March 26, 2010 at 8:13 pm

#219917

Hi,
I am having problems on my server with the backups of two of my databases (DB1 and DB2). They both reside on a SQL 2005 cluster on one instance (INST1). I backup the databases so a single file on a network shared folder (FILE.BAK).
I checked it and it has been failing for a while, with the following error:
Executing the query "BACKUP DATABASE [DB1] TO DISK = N'\\\\<i.p.>\\Network_Shared_Folder\\FILE.BAK' WITH NOFORMAT, INIT, NAME = N'DB1_backup_20100325200018', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The backup data at the end of "\\\\<i.p.>\\Network_Shared_Folder\\FILE.BAK " is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Usually when this occurred in the past, I would simply recreate the FILE.BAK file. This time, I did that and re-executed the maintenance plan for the backup. It kept running longer than it should (over two hours for a 2GB backup), so I attempted to kill the process. Hours after, I checked the status of the kill process, and it had reached 0% rollback. It will not kill!
I tried to run manual backups for DB1 and DB2. For DB1 it fails instantly with the following error:
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft.SqlServer.Smo)
This made sense to me since it is still trying to kill the process above, and I guess at the time (according to the error above) it was working on backing up the first database – DB1.
For the DB2 backup, after about two hours it was still initializing – it had not even reached 0% count! So I clicked on the ‘Stop Action Now’ link just below where you would normally see the percentage count, and it is still trying to stop that process – it has been three hours now.
So basically I have two databases that I cannot stop backups for, and I am afraid to do anything drastic (like force the kill if it is possible, or stop and start the engine) as it may corrupt something that needed rolling back. I do not have a current backup of these databases because of the errors.
Any suggestions would be great.
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

you can not back up two databases in a single file.

Are you trying to do this?

Paul White SSC Guru Points: 150467 More actions · Answer 2

vidya_pande (3/27/2010)
you can not back up two databases in a single file. Are you trying to do this?

This is incorrect.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 3

Timothy,

This type of problem (the corrupted file) is the main reason I never use a physical file for more than one backup. There's really no advantage, in any case. I would strongly recommend using separate files for each backup operation - it is easy to set this up in a Maintenance Plan, and even get it to tidy up old files for you.

As far as your current predicament is concerned, I would probably perform an orderly shut down and restart of the SQL Server service and soon as practicable. As soon as recovery is complete, take new full backups - to separate files. A stuck backup should not result in data corruption, since a backup is a read-only operation.

For clarity, each and very transaction log backup should be written to its own file too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Timothy Patihk Right there with Babe Points: 793 More actions · Answer 4

Hi,

thanks for the sound advice!

I agree with the separate files, but I have some other people to convince. 🙂

It turned out that the network share that I was writing to had no more room, but I was not getting a 'insufficient space' error. Instead I was getting a 'file seems to be corrupt' error. Once space was cleared, the backups started working again.

Actually, I did have to reboot the server(s) on the cluster to clear up the die'ing processes first.

Thanks again!

Timothy Patihk Right there with Babe Points: 793 More actions · Answer 5

Actually I am trying to setup the backups to the local drive, then write a dos script that can copy this file to the network share, replacing an exisitng file.

Any suggestions?

I do not want to turn on command execution on the SQL engine following security best practices.

Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 6

Timothy Patihk (3/29/2010)
I agree with the separate files, but I have some other people to convince. 🙂

I feel your pain, but don't give up on that. Combining backups that way is one of *the* best ways to get to an unrecoverable situation. No-one wants that.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 7

Timothy Patihk (3/29/2010)
Actually I am trying to setup the backups to the local drive, then write a dos script that can copy this file to the network share, replacing an exisitng file. Any suggestions?

SSIS Copy File Task

SQL Server Agent Job with a proxy

Separate 'DOS' batch called by the Windows scheduler

CLR function with EXTERNAL_ACCESS

...lots of options 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

P Jones SSChampion Points: 12353 More actions · Answer 8

I do exactly as you require using a simple second step after the backup. It is an Operating System (CmdExec) step which does a simple DOS copy across the network

copy "D:\SQLBackups\db_Backup.bak" "\\server\share$\Backups\db_Second.BAK"

I run it with a proxy that can execute xp_cmdshell commands.

Just ensure that the proxy account has all the access permissions to the destination folder - with a domain move in progress this is my current nightmare.

Timothy Patihk Right there with Babe Points: 793 More actions · Answer 9

Hi,

I am actually looking at the scheduled task option - I think all the others require xpshell turned on, which is not best practice.

Thanks!