July 23, 2008 at 7:06 am
I have a job that every night restores a database as
Today that job failed to work properly, and when I went in to look at it, the job was still marked as running, but won't stop. The _TEST datbase doesn't show up when I list databases (or query sys.databases). I can see the files in the temp location they get placed, and they are locked so I can't delete them.
When I try to open activity manager, I get the attached error (Database 'AZPPLUS_TEST' is being recovered. Waiting until recovery is finished. (Microsoft SQL Server, Error: 922).
I've tried detatching/dropping the database, and it tells me it doesn't exist.
Anyone know what to do? I have the problem on our dev box also, and bouncing the SQL service fixes it, but I'd rather not do that on our live box if it can be avoided.
The Redneck DBA
July 23, 2008 at 7:58 am
do you see it recovering in the list of databases?
check the sql log and it will tell you how far it's done and how much left to go
July 23, 2008 at 8:07 am
No, I can't find it in any list (in SSMS, or by hitting sys.databases).
Noting in the errorlog about it recovering. It shows it trying to attach and failing, but nothing about being stuck...just that the attach failed.
The Redneck DBA
July 23, 2008 at 8:17 am
I might also run a trace for that user and see if anything is happening.
Can you stop the job?
July 23, 2008 at 8:45 am
I can't get the job to stop, but at the same time I can't see that user doing anything.
The Redneck DBA
July 23, 2008 at 8:53 am
I was wrong. Using sp_who I could see a couple of spids with that user with suspended status and 'create database' for the command. I tried killing them, and the cmd changed to 'Killed/rollback', but still suspended.
I did get to a point where I didn't have any jobs scheduled for a little while, so bounced the agent service and got the job to stop at least.
The Redneck DBA
July 23, 2008 at 9:13 am
when the database is in the restoring state try the following command and see if the database comes back online
alter database mydata with recovery
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2008 at 9:18 am
That statement won't even parse.
The Redneck DBA
July 23, 2008 at 10:31 am
sorry my bad!!
restore database DBNAME with recovery
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2008 at 10:35 am
Interesting. I get the following message when I run that:
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
At least it seems like an acknowledgement that the database exists. I can't see any connections to it using sp_who(2), it doesn't show up in SSMS, and it doesn't show up in select * from sys.databases.
Very odd.
The Redneck DBA
July 23, 2008 at 10:42 am
Try to drop the database with 'drop database' statement then restore from backup. If you have a valid BAckup.
Maninder
www.dbanation.com
July 23, 2008 at 10:44 am
I already tried dropping, it just says it doesn't exist.
And all this database is used for is a test DB to run a DBCC against. I really don't even need it.
The Redneck DBA
July 23, 2008 at 10:47 am
try to reboot the server or restart sql
i had something similar twice. one time the db went suspect because of no space on the drive. another time server rebooted and it was recovering, and had the same exclusive lock error. on the reboot don't click the db in SSMS, follow it in the sql log.
July 23, 2008 at 10:56 am
Jason Shadonix (7/23/2008)
Interesting. I get the following message when I run that:Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
At least it seems like an acknowledgement that the database exists. I can't see any connections to it using sp_who(2), it doesn't show up in SSMS, and it doesn't show up in select * from sys.databases.
Very odd.
what was the state of the database when you ran the command?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2008 at 10:58 am
No idea. Since it doesn't show up anywhere I don't know how to tell.
Perry Whittle (7/23/2008)
Jason Shadonix (7/23/2008)
Interesting. I get the following message when I run that:Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
At least it seems like an acknowledgement that the database exists. I can't see any connections to it using sp_who(2), it doesn't show up in SSMS, and it doesn't show up in select * from sys.databases.
Very odd.
what was the state of the database when you ran the command?
The Redneck DBA
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply