July 24, 2012 at 8:33 am
Last night my backups failed on my maintenance plan and looks like my Model database was corrupted. It was restoring the database and now it stuck on Restore. If I select Model database and go to Properties in the status: Restoring. The server is running SQL 2008 R2, just a couple of days old, I tried to restore from previous date but it won't allow me to do it because its in the status of restoring.
I have tried this:
use model
restore database model
with norecovery
output:
Msg 927, Level 14, State 2, Line 1
Database 'model' cannot be opened. It is in the middle of a restore.
July 24, 2012 at 8:34 am
What exactly happened? (maintenance plans can't restore model)
What is the exact state of model?
What is the exact state of the sQL instance?
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
July 24, 2012 at 8:40 am
I thought so, but I no expert in SQL.
So my users are able to insert records into another database in SQL, but when I perform the maintenance plan to run it, it errors out:
Executing the query "USE [model]
" failed with the following error: "Database 'model' cannot be opened. It is in the middle of a restore.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I not able to restore back to previous date or stop the restore process.
July 24, 2012 at 8:43 am
Please check the SQL error log for more information. you should in all probability find some clues there.
July 24, 2012 at 8:46 am
DO NOT restart SQL!
Look in the SQL error log. Sounds like someone's been fiddling with things they don't understand. Look for any and all messages relating to model.
p.s. A maintenance plan cannot restore databases. It can back up.
Has anyone changed that maint plan recently? Check and see if it's trying to do log backups on model.
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
July 24, 2012 at 9:12 am
Okay I haven't restarted the server.
It's only me that been touch this server.
So not sure what to look for now.
July 24, 2012 at 9:27 am
Here is something from my log files. I do run BackupExec from Symantec to do my nightly backups to tapes.
Log Name: Application
Source: Backup Exec
Date: 7/23/2012 11:08:44 PM
Event ID: 57859
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: VENSQL01.com
Description:
An error occurred while executing the following query: "DBCC CHECKDB([model]) WITH PHYSICAL_ONLY".
On server: "VENSQL01".
SQL error number: "039F".
SQL error message: "Database 'model' cannot be opened. It is in the middle of a restore.
".
For more information, click the following link:
http://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Backup Exec" />
<EventID Qualifiers="8192">57859</EventID>
<Level>2</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2012-07-24T03:08:44.000000000Z" />
<EventRecordID>6071</EventRecordID>
<Channel>Application</Channel>
<Computer>VENSQL01.com</Computer>
<Security />
</System>
<EventData>
<Data>DBCC CHECKDB([model]) WITH PHYSICAL_ONLY</Data>
<Data>VENSQL01</Data>
<Data>039F</Data>
<Data>Database 'model' cannot be opened. It is in the middle of a restore.
</Data>
<Binary>9F030000000000000000000000000000</Binary>
</EventData>
</Event>
July 24, 2012 at 9:27 am
What happens if you issue
RESTORE DATABASE model WITH RECOVERY
from another process?
July 24, 2012 at 9:29 am
What is in the maintenance plan? Is there a T-SQL script that is there to restore something? There are no tasks in maintenance plans to do restores, as Gail mentioned.
If you are the only one that touches the server, then you started the restore. There's no way for a restore to start by itself. Did you attempt any restore?
July 24, 2012 at 9:40 am
clayman (7/24/2012)
What happens if you issue
RESTORE DATABASE model WITH RECOVERYfrom another process?
Results:
RESTORE DATABASE successfully processed 0 pages in 0.109 seconds (0.000 MB/sec).
Then run the Maintenace plan again,
It failed, and starting to Restore process the Model database again.
Ran restore database model with recovery again then check database results:
DBCC results for 'model'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 632 rows in 7 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 91 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 103 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 130 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 53 rows in 1 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 483 rows in 8 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 134 rows in 2 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 286 rows in 2 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 136 rows in 17 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 146 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 106 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 97 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 99 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 18 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'model'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator
July 24, 2012 at 9:58 am
How this happened: Someone ran BACKUP LOG model WITH NORECOVERY
You should see some evidence of this in the log. Find whoever was messing with model and have a stern word with them.
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
July 24, 2012 at 10:10 am
I deleted my Maintenance Plan and only selected the databases that I created and ignore Master, and model for the time being.
After running the plan it error out in the last part for the Maintenance Cleanup Task:
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2012..." failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
July 24, 2012 at 10:18 am
gkuiper (7/24/2012)
I deleted my Maintenance Plan and only selected the databases that I created and ignore Master, and model for the time being.
If that's a backup maint plan, you should revise that and either include master and model or create another maint plan to back them up.
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
July 24, 2012 at 10:33 am
I have put Master and model on a separate plan.
With this I still having the plan fail at the end for clean up task.
July 24, 2012 at 10:53 am
Just wanted to thank everyone for helping me out. Found out the issue and fixed it. The clean up was missing the file extension and location, once it was added worked without errors.
Cheers.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply