December 16, 2010 at 6:42 pm
Hi
A backup file command was issued on one of the databases, then terminated. It has retreated into the killed/rollback state, and is sitting there doing nothing. No cpu, no io.
Backup file still locked, in use and the estimated time on kill is racing upwards. Backup file was a \\servername\filename network location.
Obviously my transaction log is now going ape, being unable to truncate itself, though it looks like the transaction log backups are at least still viable.
Any suggestions? What size hammer should I use? I'm currently thinking perhaps restart the file server to try and convince sql the connection is broken and it should abandon all hope. Failing that, restart of sql? My big concern is of course whether that will lead to sql abandoning far too much hope and marking the db as suspect, or keeping it unavailable for 6 hours during restart!
December 16, 2010 at 7:28 pm
Before giving you a sugestion, what was the actual (full) error message you received?
A suggestion, if possible, backup locally then move to a network location.
December 16, 2010 at 10:24 pm
No error message, afaik the client hasn't returned an error yet.
December 16, 2010 at 10:25 pm
Cant run a backup as they are serialisable, and we have to wait for the last one to complete...
December 16, 2010 at 10:36 pm
RichB (12/16/2010)
Cant run a backup as they are serialisable, and we have to wait for the last one to complete...
???
December 16, 2010 at 11:14 pm
For what it is worth a cluster failover was initiated during our activity trough and completed swiftly. The zombie connection cleaned up effectively instantly and hopefully everything is now ok - at least the 50GB transaction log is no longer 99% full, and indeed is back down to a couple of gb 🙂
December 16, 2010 at 11:16 pm
Lynn Pettis (12/16/2010)
???
That is what it says... can't run a db backup while one is already running!
December 17, 2010 at 6:08 am
First, unless there are io system issues that prevent it, running backup of multiple databases simultaneously shouldn't be a problem.
Second, my suggestion really wasn't to run simultaneous backups, but backup the databases locally first and then move those backup files to the network storage. This will keep network issues from affecting the database backups as SQL Server is not very forgiving to even minor network delays where as a network file copy is.
December 17, 2010 at 9:49 am
Perhaps I was unclear.
There is 1 database in question.
Command 1 spid 313 = Backup database x with file = y to disk = \etwork\z
this hung, was killed and entered killed/rollback, zombie no io no cpu, destination file z still locked.
Command 2 spid 414 = Backup database x with file = y to disk = \etwork\z_1
failed due to backup commands being serialised.
Server restart fixed spid 313 everything now fine.
R
December 18, 2010 at 4:06 pm
RichB (12/17/2010)
Perhaps I was unclear.There is 1 database in question.
Command 1 spid 313 = Backup database x with file = y to disk = \etwork\z
this hung, was killed and entered killed/rollback, zombie no io no cpu, destination file z still locked.
Command 2 spid 414 = Backup database x with file = y to disk = \etwork\z_1
failed due to backup commands being serialised.
Server restart fixed spid 313 everything now fine.
R
Actually, you were quite clear. There was apparently no error messages that you could provide. The backups are run sequentially and one can't run until the other has completed. Your backups are done to a network file server.
Did I miss anything?
My suggestions were simple. One, do your backups to a local directory on the server and then move the backup files to the network file server. Two, there really is no reason that multiple backups can not be accomplished in parallel unless your IO subsystem will not handle the load.
December 18, 2010 at 6:53 pm
Actually, you were quite clear. There was apparently no error messages that you could provide. The backups are run sequentially and one can't run until the other has completed. Your backups are done to a network file server.
Did I miss anything?
My suggestions were simple. One, do your backups to a local directory on the server and then move the backup files to the network file server. Two, there really is no reason that multiple backups can not be accomplished in parallel unless your IO subsystem will not handle the load.
Clearly not, as the sql server refuses to back up a database while it is already being backed up. Perhaps you should try it.
Thanks anyway.
December 18, 2010 at 8:40 pm
RichB (12/18/2010)
Actually, you were quite clear. There was apparently no error messages that you could provide. The backups are run sequentially and one can't run until the other has completed. Your backups are done to a network file server.
Did I miss anything?
My suggestions were simple. One, do your backups to a local directory on the server and then move the backup files to the network file server. Two, there really is no reason that multiple backups can not be accomplished in parallel unless your IO subsystem will not handle the load.
Clearly not, as the sql server refuses to back up a database while it is already being backed up. Perhaps you should try it.
Thanks anyway.
Are you saying that you are running multiple backups of the same database? Are you backing up multiple databases to same backup file? If so, these are things you did not articulate in your post.
I have backed up multiple databases simulatanously in many instances. I have also ensured that all backups are done locally and that those backup files are then moved to another server from which they are subsequently backed up to tape for off-site storage.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply