May 20, 2019 at 2:01 pm
Every morning, the Prod Backup file is copied to the Reporting server, and restored. This morning, the restore step seems to have completed, but the step shows the following error, causing the overall job to fail, so subsequent steps did not run.
Thoughts on the cause(s) for this on a just restored DB ?
Error:
Executed as user: Servername\Administrator. Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. [SQLSTATE 01000] (Message 5060) Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. [SQLSTATE 01000] (Message 5060). The step succeeded.
May 21, 2019 at 3:57 am
That's an error from taking a database into single user mode. That shouldn't normally be a part of a restore process unless people on your server have too many permissions and you're taking it into single user before you restore in order to lock people out. In which case, it could be anything, but it's going to focused around someone else had locks in the system within transactions that had to be rolled back. They did complete successfully. That's the good news.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2019 at 9:29 am
This statement is right before the restore, and has been for years without problems, to prevent "Database in use" type errors
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
Is there a better way to restore a backup, and ensure it happens, regardless of who is connected, or any activity ?
May 21, 2019 at 11:17 am
Well, the "regardless of who is connected" bit is what you've run into. There was some sort of open transaction and it got rolled back successfully. I'd just suggest changing the error trapping in your scripts. Since you want to restore AND kick everyone off, that's your best bet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2019 at 12:06 pm
It's happened two days in a row now. Maybe killing any SPIDs found in sysprocesses first ?
May 21, 2019 at 2:59 pm
Killing SPIDs just puts them into rollback, same as this. I'd be more concerned about what's happening, what has changed, what's new that something that has ran for years without incident has had problems two days in a row (one day, wouldn't bother me at all, two is a developing pattern).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2019 at 6:06 am
I'm a moron. Somehow the job step was set to fail the job, even on success. Not sure how that happened.
May 22, 2019 at 8:58 am
Ha! Yeah, that kind of thing happens to all of us. Random mouse click, sunspots, it happens.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2019 at 3:15 pm
Hi there,
I would like to know the solution for the error which happened on our DB restoring process this morning. It used to work well to refresh the Dev DB from the Prod DB, but suddenly we got the error which has the verbiage like this following:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. [SQLSTATE 01000] (Message 5060) Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. [SQLSTATE 01000] (Message 5060) Cannot drop database "Test" because it is currently in use. [SQLSTATE 42000] (Error 3702). The step failed.
Right, our first step of the restoration has this script:
ALTER DATABASE Test
set SINGLE_USER WITH ROLLBACK IMMEDIATE
Drop database Test
Please help. Thanks.
October 23, 2019 at 5:58 pm
Hi there,
I would like to know the solution for the error which happened on our DB restoring process this morning. It used to work well to refresh the Dev DB from the Prod DB, but suddenly we got the error which has the verbiage like this following:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. [SQLSTATE 01000] (Message 5060) Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. [SQLSTATE 01000] (Message 5060) Cannot drop database "Test" because it is currently in use. [SQLSTATE 42000] (Error 3702). The step failed.
Right, our first step of the restoration has this script:
ALTER DATABASE Test
set SINGLE_USER WITH ROLLBACK IMMEDIATE
Drop database Test
Please help. Thanks.
You should start a new thread - this one was answered back in May.
The reason your step failed is because another process accessed the system between the ALTER statement and the DROP statement - taking the one allowed connection.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 24, 2019 at 12:53 pm
...taking the one allowed connection
Which might be the AUTO_UPDATE_STATISTICS_ASYNC process:
Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.
October 24, 2019 at 8:50 pm
Thanks for the solutions from all of you. Very helpful, and I appreciated it very much.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply