December 17, 2009 at 12:52 pm
Started a restore of a large database from management studio.
seeing this error in the log:
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
I thought it may be running out of space so I clicked on the 'end this operation now' on the restore window.
it seems to be stuck in a 'restoring' state for a couple of hours now.. Can anyone help?
December 17, 2009 at 12:54 pm
The message in the error log was expected. Restoring a database does clear a couple of caches, so anytime you do a restore you will see three similar messages.
Best thing to do would be to restart the restore completely.
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
December 17, 2009 at 1:01 pm
the 'stuck in restore' state? will that eventually roll back or is there a way to kill it completely?
December 17, 2009 at 1:11 pm
Depends. Is there a process still restoring the DB (or rolling back the restore)?
Easiest way, as I said, is just to restore again. Drop that DB or restore over the top of it.
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
December 17, 2009 at 2:42 pm
Can you run this query and post the results? If restore is still happening this query will give us details what exactly is happening.
select s1.session_id,s1.command,s1.percent_complete,s1.wait_type,s1.wait_time,s1.wait_resource,s2.text from master.sys.dm_exec_requests s1
CROSS APPLY master.sys.dm_exec_sql_text(sql_handle) AS s2
where s1.percent_complete> 0
December 17, 2009 at 2:59 pm
A simple sp_who2 will tell you if there is a restore running. In this case there will not be as you stopped the restore job. If you kill a restore job or it fails the database will show as 'restoring'. The database will remain in that state until you start a restore again and it completes.
there is nothing to rollback.
---------------------------------------------------------------------
December 17, 2009 at 4:52 pm
ran the query you posted and it ran successfully but returned NO results.
The database is still in a 'restoring' state. I attempted to rerun the restore and I got this error:
Sourcespid57
Message
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'F:\MSSQL\AML\LogFiles\PBSA\PBSALog_KOC.dat'.
Should I stop and start the instance?? Try and drop the database?? not sure what to do at this point.. any help???
December 17, 2009 at 6:55 pm
Unfortunately you may be at the stop and start services point now. Of course the db will probably come up suspect, but you should be able to either delete it or restore over it then.
MD
Marvin Dillard
Senior Consultant
Claraview Inc
December 17, 2009 at 7:51 pm
Marvin,
I stopped and restarted the instance. it complained alot but it finally came down and back up again. The DB came up suspect but I was able to drop it. I then successfully reran the restore..
Thank you everyone.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply