SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Stuck Recovering


Database Stuck Recovering

Author
Message
TheRedneckDBA
TheRedneckDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12577 Visits: 2652
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
Attachments
error.jpg (269 views, 26.00 KB)
alen teplitsky
alen teplitsky
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27103 Visits: 4910
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
TheRedneckDBA
TheRedneckDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12577 Visits: 2652
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (603K reputation)SSC Guru (603K reputation)SSC Guru (603K reputation)SSC Guru (603K reputation)SSC Guru (603K reputation)SSC Guru (603K reputation)SSC Guru (603K reputation)SSC Guru (603K reputation)

Group: Administrators
Points: 603056 Visits: 21111
I might also run a trace for that user and see if anything is happening.

Can you stop the job?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TheRedneckDBA
TheRedneckDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12577 Visits: 2652
I can't get the job to stop, but at the same time I can't see that user doing anything.

The Redneck DBA
TheRedneckDBA
TheRedneckDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12577 Visits: 2652
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212640 Visits: 18586
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" ;-)
TheRedneckDBA
TheRedneckDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12577 Visits: 2652
That statement won't even parse.

The Redneck DBA
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212640 Visits: 18586
sorry my bad!!

restore database DBNAME with recovery

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
TheRedneckDBA
TheRedneckDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12577 Visits: 2652
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search