2

On my SQL server 2008, i have a SQL agent job to restore a database on nightly basis. Procedure:

  1. find latest backup on other server
  2. Kill all conenction to the destination database
  3. Restore destination database with replace, recovery

It failed last weekend because the database was being used by a system process (spid 11 checkpoint). since I couldnt kill the system process, I fixed this by restarting sql server. It failed this weekend as well with same error (checkpint process in this database as from sp_who) and when I run:

SELECT session_id,request_id,command,status,start_time
FROM sys.dm_exec_requests 
WHERE session_id = 11

It shows:

11  0   CHECKPOINT  background  2010-04-06 10:17:49.103

I cant restart the server every time it fails.
Can anyone please help me in fixing this?

Thanks in advance

Manjot

3

Don't kill SPIDs less than 50! These are system processes

To see if you can take the DB out of the "restoring" state try

RESTORE DATABASE MyDB WITH RECOVERY

Edit, after comment

Assuming you want to restore the DB and don't care about keeping it...

Stop SQL Server, move the MDF/LDF somewhere else, start SQL Server.

The DB comes up offline (I forget the exact status). Then you can drop it, restore it

  • Tried this. since the no log file was restored on top, I doesn't let me do this. :-( – Manjot Apr 12 '10 at 5:36
  • yes i am sure that it will be fixed after restart. It got it fixed last time when i restarted. But i cant restart the server everytime for this. Is there no way that i can fix it without a restart? – Manjot Apr 12 '10 at 21:40
  • @manjot: It sounds like your restore process is broken somehow. I assumed it was a one off you wanted to fix. You could DROp before restore everytime though – gbn Apr 13 '10 at 4:27
  • If there is a system process,which you cant kill. You cant even drop the database because drop doesnt work if there is an open connection. Isnt it? – Manjot Apr 13 '10 at 5:12
  • Have you tried it? – gbn Apr 13 '10 at 6:39
1

fixed- since it was restored daily. i have excluded it from re-index, dbcc checkdb, update stats etc maintenance plans.

even though they ran during seperate schedule, excluding the database fixed the issue.

1

Rather than killing the open connections, try

ALTER DATABASE myDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Then do the restore, or better yet, drop it, then restore.

After you're done, don't forget to return the database to its previous state:

ALTER DATABASE myDatabase SET MULTI_USER
  • As my database is in restoring state it says "ALTER DATABASE is not permitted while a database is in the Restoring state" Can't drop it as it is in use by this SPID. – Manjot Apr 12 '10 at 3:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.