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
Share
Follow
asked Apr 21 "14 at 18:52
*

HotelsingerHotelsinger
14211 gold badge33 silver badges1111 bronze badges
1
Add a comment |

2 Answers 2


Active Oldest Votes
6
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).


Share
Follow
answered Apr 21 "14 at 20:45
*

Bacon BitsBacon Bits
26.5k55 gold badges5050 silver badges5858 bronze badges
Add a comment |
4
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:

USE master;goALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEgoALTER DATABASE FooFile SET MULTI_USER;go
Share
Follow
answered Apr 24 "14 at 22:56

*

HotelsingerHotelsinger
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
Submit

Article as a guest


Name
Email Required, however never before shown


Message as a guest


Name
Email

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
Linked
2
Kill just user processes in SQL Server
Related
1
SQL Auth user that deserve to restore some (however not all) databases
1398
How deserve to I carry out an UPDATE statement via JOIN in SQL Server?
87
Android backup/restore: how to backup an inner database?
1425
How deserve to I delete using INNER JOIN through SQL Server?
5
SQL Transactivity Log Shipping stops working to restore database to standby
3
Error as soon as trying to reclaim SQL Server 2016 backup on SQL Server 2014 through compatibility mode 110
0
Stored procedure works once exec, stops working in agent project
0
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.


*

lang-sql
Stack Overflow
Products
Company
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.