On my SQL server 2008, i have a SQL agent job to restore a database on nightly basis. Procedure:
- find latest backup on other server
- Kill all conenction to the destination database
- 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
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