Only user processes can be killed

We have an SQL Job Agent that runs in the "wee hours" to regain our regional database (FooData) from a manufacturing backup.

First, the database is collection to SINGLE_USER mode and also any type of open processes are eliminated. Second, the database is recovered.

You watching: Only user processes can be killed

But the third step stops working periodically through Error 6107: "Only User Processes Can Be Killed"

This happens about as soon as or twice a week at seemingly random intervals. Here is the code for action 3 where the faientice periodically occurs:

USE master;goexec msdb.dbo.KillSpids FooData;goALTER DATABASE FooFile SET MULTI_USER;goDoes anybody have any ideas what can be arising to cause this error? I"m reasoning there can be some automated process beginning up during action 3 or probably some user trying to log in throughout that time? I"m not a DBA, so I"m guessing at this suggest, although I think that a user should not have the ability to log in while the DB is in SINGLE_USER mode.

sql-server database-reclaim single-user
asked Apr 21 "14 at 18:52

14211 gold badge33 silver badges1111 bronze badges
Add a comment |

2 Answers 2

Active Oldest Votes
A user more than likely isn"t logged in. The system is probably perdeveloping some job. The output of exec sp_who or sp_who2 will show what sessions are open up. Any SPID listed below 50 is a system procedure, and also cannot be eliminated through KILL. The just method to sheight them is to speak the SQL Server organization or worry a SHUTDOWN command (which does the exact same thing).

answered Apr 21 "14 at 20:45

Bacon BitsBacon Bits
26.5k55 gold badges5050 silver badges5858 bronze badges
Add a comment |
I uncovered the answer to my problem by transforming one line of code which functioned prefer a cdamage.

See more: Uninstall And Remove Spywareblaster Should I Remove It H Windows Essentials?

As pointed out in the original question, the "KillSpids" line is offered in Tip 1 of the job. (Along with SET SINGLE USER) The "KillSpids" made feeling in Step 1 because tbelow may be unwanted processes still energetic on the database.

The "KillSpids" line was then included aget into Tip 3, yet it was unessential, and also was likewise resulting in the 6107 error.

I reinserted the "KillSpids" line with the one displayed listed below. Setting the freshly brought back database to single user mode takes treatment of the worry that a user might attempt to log in before all the project procedures have been completed. Here is the updated code:

answered Apr 24 "14 at 22:56


14211 gold badge33 silver badges1111 bronze badges
Add a comment |

Your Answer

Thanks for contributing a response to Stack Overflow!

Please be sure to answer the question. Provide details and also share your research!

But avoid

Asking for aid, clarification, or responding to various other answers.Making statements based upon opinion; ago them up via referrals or individual suffer.

To learn more, view our tips on creating great answers.

See more: My Ssd Keeps Filling Up My Ssd Drive And I Can'T Figure Out Why

Draft saved
Draft discarded

Sign up or log in

Sign up making use of Google
Sign up using Facebook
Sign up utilizing Email and also Password

Article as a guest

Email Required, however never before shown

Message as a guest


Required, yet never shown

Post Your Answer Discard

By clicking “Article Your Answer”, you agree to our regards to service, privacy policy and also cookie plan

Not the answer you're looking for? Browse various other inquiries tagged sql-server database-restore single-user or ask your own question.

The Overcirculation Blog
Featured on Meta
Visit chat
Kill just user processes in SQL Server
SQL Auth user that deserve to restore some (however not all) databases
How deserve to I carry out an UPDATE statement via JOIN in SQL Server?
Android backup/restore: how to backup an inner database?
How deserve to I delete using INNER JOIN through SQL Server?
SQL Transactivity Log Shipping stops working to restore database to standby
Error as soon as trying to reclaim SQL Server 2016 backup on SQL Server 2014 through compatibility mode 110
Stored procedure works once exec, stops working in agent project
SQL Server database keeps setting SINGLE_USER on in tests
Hot Network-related Questions even more hot questions

Inquiry feed
Subscribe to RSS
Concern feed To subscribe to this RSS feed, copy and paste this URL into your RSS reader.


Stack Overflow
Stack Exadjust Network-related
website style / logo design © 2021 Stack Exreadjust Inc; user contributions licensed under cc by-sa. rev2021.4.7.38999

Stack Overflow functions ideal with JavaScript permitted

Your privacy

By clicking “Accept all cookies”, you agree Stack Exadjust deserve to store cookies on your tool and also disclose information in accordance via our Cookie Policy.