Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot backup - Old killed backup still running Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:34 AM
Points: 104, Visits: 405
Hi

I have one DB that on a multi DB server that people cannot connect to. I'm not familar with the DB.

I THINK the problem was that an reindex or backup was ongoing when the Server was restarted.

If I try and backup the DB now I get this error:
System.Data.SqlClient.SqlError: Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft.SqlServer.Smo)

I can find 2 processes which have been killed: KILLED/ROLLBACK status=SUSPENDED. Both have wait time (ms) of a couple of days

kill 85 with statusonly
SPID 85: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

How can I remove these so I may get the DB back up and working. Would a SQL restart work..

Thanks



Post #1434884
Posted Monday, March 25, 2013 7:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Restart the SQL instance.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1434902
Posted Monday, March 25, 2013 7:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:34 AM
Points: 104, Visits: 405
Thanks for the Reply.

I really don't understand how this could have happened. These jobs are being rolled back from Friday/Sat morn. Its now mon.

I can select from tables in the DB but users cannot log into the App (of which I know nothing).

I will have to wait until people go home, just in case it takes a while to restart.

I have a backup but I guess I would be able to restore that while its in this state.



Post #1434908
Posted Monday, March 25, 2013 7:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
You won't be able to drop or restore the database, just restart SQL. Shouldn't take any recovery time at all.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1434916
Posted Monday, March 25, 2013 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:36 AM
Points: 36, Visits: 718
re-start backup may be a good idea if you can't restart the server(sql server) now or restart sql server and backup both when you can do that.
Post #1434931
Posted Monday, March 25, 2013 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:34 AM
Points: 104, Visits: 405
Long and short the whole server has been restarted, SQL started ok. these Backup processes have disappeared.

Thanks for your help.

It didn't take any time to restart.

Post #1434978
Posted Tuesday, March 26, 2013 7:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 1,635, Visits: 5,592
GilaMonster (3/25/2013)
You won't be able to drop or restore the database, just restart SQL. Shouldn't take any recovery time at all.


I think that's the first time I've ever seen you give the advice to just restart the server to fix the issue--is this sort of thing a known problem in SQL Server?
Post #1435455
Posted Tuesday, March 26, 2013 7:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Not a known issue in that there's a kb and a pile of documentation. I've seen it in person once, seen it on the forums since 3 or 4 times since. I don't know what causes it (and until I can repo on demand probably won't be able to figure it out), but a restart removes the rolling back backup with no long recovery required.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1435463
Posted Tuesday, July 23, 2013 3:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:15 AM
Points: 187, Visits: 376
we are facing same error as below....

not able to kill that process .................

Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.


is there any solution except to restart sql server instance


Raj Acharya
Post #1476424
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse