Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

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

share|improve this answer
    
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
show 1 more comment
up vote 1 down vote accepted

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.

share|improve this answer
add comment

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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