Home / server / Questions / 131468
Accepted
Manjot
Asked: 2010-04-12 15:56:15 +0800 CST

SQL Alter database failed - being used by checkpoint process

  • 772

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

sql-server-2008 blocking

3 Answers

  1. 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

    • 3

  2. 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
    • 1
  3. Best Answer

    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