February 11, 2016 at 4:40 am
Hi,
I'm experiencing a problem with a SQL Server whereby the databases seem to change to 'restoring' state.
It's not all databases on the server, but normally up to about 7 of the 12 databases.
We can bring the databases back online by either restoring the last good backup, or by running 'restore database XYZ with recovery', at which point everything is fine.
The last time we brought them back online, they changed back to restoring state within about 10 minutes.
I've checked msdb..restorehistory to see if someone is genuinely issuing restore database commands, and i've checked sys.dm_exec_requests to see if there is a restore database in progress, but I can't find any references to a genuine restore occurring.
There had been a database corruption on this server a few weeks ago. DBCC CHECKDB was identifying a lot of issues.
We restored to backups of the relevant databases, and DBCC completes successfully on every database (incl. master)
However it feels like too much of a coincidence, so I feel the corruption must somehow be relevant.
Has anyone got any thoughts as to why/how a database would automatically change to 'restoring' status?
I'm tempted to restore the master database from before the corruption occurred, even though it has always passed a DBCC CHECKDB.
Any other ideas?
Thank you
February 11, 2016 at 5:04 am
Someone\something is running BACKUP LOG <db name> WITH NORECOVERY as that backs up the log and then switches the DB into the RESTORING state.
Probably a backup dialog or job where someone isn't paying attention and checked the 'tail of the log' option without knowing what it does.
It's not corruption. Corruption causes high severity errors and, worst case, sends the DB SUSPECT or RECOVERY_PENDING, not RESTORING. Restoring master won't do a thing and SQL won't do this automatically.
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
February 11, 2016 at 5:47 am
That certainly makes sense. I wasn't aware of a transaction log backups with norecovery, and had focused on it being a restore with norecovery.
I'm waiting for the server to be brought back online, and then I'm hoping that some useful information will be recorded in the msdb..backupset table to help determine how this is happening.
Thanks as always for your help
February 11, 2016 at 7:49 am
Just to confirm.
The msdb.backupset table did show that there were transaction log backups that were taking the database offline (i.e. restoring state)
Those backups were being run by the standard SQL maintenance plan and the option to backup the tail and leave in restoring state was not enabled.
To prove this is the problem, we've disabled the job for a period of time to make sure no other log backups are running, and then we'll recreate the maintenance plan completely
Thank you for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply