Only user processes can be killed. (Microsoft SQL Server, Error: 6107)

Question

Post reply

Only user processes can be killed. (Microsoft SQL Server, Error: 6107)

dndaughtery

SSChampion

Points: 11290
More actions
March 22, 2010 at 10:29 am

#218075

I was running a ssis package lodaing data over the weekend and when I came in this morning the server wsas shut down. I assume there was a electricity outage. I luckily have a backup from just before I began the load. When I try to restore the database to the last backup file I get the following error message
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'TEST-SQL-1'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I try to disconnect the users by detaching and reattaching and get the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop all active database connections failed for Server 'TEST-SQL-1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+all+active+database+connections+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Only user processes can be killed. (Microsoft SQL Server, Error: 6107)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=6107&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
What can I do to back up the database to my backup file?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dndaughtery SSChampion Points: 11290 More actions · Answer 1

I disabled SQL Server Agent and it seems to be backing up now.

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 2

You try setting the Db to single user mode and then restoring the DB

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

After restore..

ALTER DATABASE MyDB SET MULTI_USER

Please note that any active transaction will be rolled back!!!

dndaughtery SSChampion Points: 11290 More actions · Answer 3

My mistake, I meant to say it was restoring to the backup file after disabling the SQL Server Agent