Recently I have been working on a project in which we have been implementing caching through the use of the SqlCacheDependency object, something that I think goes grossly unrecognized by many developers. However, one of the minor problems we encountered was that some of our jobs began to fail. A great start for anyone interested can be found here: http://code.msdn.microsoft.com/linqtosqlcache
Our history showed a failure message:
Executed as user: NT AUTHORITY\SYSTEM. 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.
After some digging today, I found that the following statement helped resolve the issue.
ALTER DATABASE MYDATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do your restore here
-- Make the DB multi user again
ALTER DATABASE MYDATABASE SET MULTI_USER
