August 2, 2011 at 3:39 pm
I get the following error message when trying to backup this one specific database.
System.Data.SqlClient.SqlError: 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)
When I check the the Activity monitor, I see three same spid as below
id User Db Status Open Tran Command App WaitTime
128 serviceaccount db1 Suspended 0 KILLED/ROLLBACK Litespeed for SQL Srv 1122692562
128 serviceaccount db1 Runnable 0 KILLED/ROLLBACK Litespeed for SQL Srv 0
128 serviceaccount db1 Running 0 KILLED/ROLLBACK Litespeed for SQL Srv 0
When I try to kill 128, this is what I get
SPID 128: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
August 2, 2011 at 3:40 pm
Did you try contacting the vendor? "Might" have better luck for a fast answer.
August 2, 2011 at 3:43 pm
Restart the SQL service.
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
August 2, 2011 at 3:45 pm
Havent done that, as I have almost 20 database's on this server that are being backed up using the same tool as well but there has been no problem on those.
Even if I do a native backup using TSQL or right click database 'Backup' , it does not respond and gives me the error message.
August 2, 2011 at 3:47 pm
Is there any other solution that I can try before restarting the service, as I have may more application database on this server ?
Any idea why this sould have happend ?
August 2, 2011 at 3:48 pm
LOOKUP_BI-756009 (8/2/2011)
Is there any other solution that I can try before restarting the service, as I have may more application database on this server ?Any idea why this sould have happend ?
Is this the first time it happens or this is recurring?
August 2, 2011 at 3:56 pm
LOOKUP_BI-756009 (8/2/2011)
Is there any other solution that I can try before restarting the service, as I have may more application database on this server ?
You could wait, but last time I saw this 2 days later it hadn't resolved itself
Any idea why this sould have happend ?
Not at the moment, it's a stuck rollback, seen it on normal transactions, seen it a few times on log or database backups. Never managed to repo on demand though.
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
August 3, 2011 at 8:09 am
Yup, This rollback is from a job that failed last week. Never looked like the rollback is actually happening.Its been going on since then.
August 3, 2011 at 8:09 am
1st time for this specific database, nvr saw this before on others
March 3, 2012 at 6:42 am
Hi I think I am too late but I also faced the issue at my production envirnment and i fix it by doing this.
1-execute the below T-sql it will give you the spid which can be culpit.
select session_id, Text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle)
2-once you get the spid you can kill the spid and start the backup.
In my case one more instance of backup was running using 3rd party tool for tape backup.So I abort that backup and started the backup and it got successed.
Regards
Ajay Bhardwaj
March 8, 2012 at 9:54 am
Grasshopper, this was my problem. Thanks much for providing the solution.
CB
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply