We have to do a lot of restore from backups for a development on a production sql 2008 server.
This daily job or by request job will have 3 steps.
Step 1 kill the user that was using such @dbname
declare killprocess_cursor cursor for
select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@dbname
Step 2 Restore database, from a fixed path using a T-Sql
RESTORE DATABASE db_abc FROM DISK = 'H:\RestoreDB\db_abc_FullBK.bak' with replace
Step 3 restore users
Sometimes the daily job will fail at 2. The by request job will fail at 1.
I am concern with daily job failure now. Error message:
Executed as user: myDomain\myISAgentAcct. Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
When I use sp_who2, there is none using the db and rerun result with the same error. I have to take it offline, detach, attach again and rerun the job to finish it without error.
What bugs me... the job will run for in most days and fail in other. I already has a kill user step and no one works at the schedule night time.
My last suspicion... either Report service has a hold on
View Complete Post