SQL Job Agent DB Restore fails with error #6107: Only user processes can be killed
We have an SQL Job Agent that runs in the "wee hours" to restore our local database (FooData) from a production backup.
First, the database is set to SINGLE_USER mode and any open processes are killed. Second, the database is restored.
But the 3rd step fails occasionally with Error 6107: "Only User Processes Can Be Killed"
This happens about once or twice a week at seemingly random intervals. Here is the code for step 3 where the failure occasionally occurs:
USE master;
go
exec msdb.dbo.KillSpids FooData;
go
ALTER DATABASE FooData SET MULTI_USER;
go
Does anybody have any ideas what might be occurring to cause this error? I'm thinking there might be some automated process starting up during step 3 or possibly some user trying to log in during that time? I'm not a DBA, so I'm guessing at this point, although I believe that a user should not be able to log in while the DB is in SINGLE_USER mode.
Answer
A user probably isn't logged in. The system is probably performing some task. The output of exec sp_who or sp_who2 will show what sessions are open. Any SPID below 50 is a system process, and cannot be killed with KILL. The only way to stop them is to stop the SQL Server service or issue a SHUTDOWN command (which does the same thing).