12

This is a duplicate of the question I asked on stackoverflow, but I was advised, that someone here could have a better idea what is happening.

I have a sporadic problem, when upgrading SQL Server in a single-user mode, using .NET SqlConnection, some other application somehow logs into the database, while the SQL code is being executed, and kicks my process out. SqlConnection is not closed or disposed in any way. But some other application somehow ends up connected to the database and that kicks my connection out.

When I run sp_who, I could see that a process that took control of the database is Command="TASK MANAGER".

Anyone could tell me what is this process, what is it's purpose, and how in the world it could get into a database, which is in single-user mode, and there is an active connection?

  • Do you shut down SQL Agent during this process? The tasks named "TASK MANAGER" (with low spid numbers) are internal processes. – Jon Seigel Jul 11 '12 at 16:45
  • @JonSeigel no, I didn't. Are you saying SQL Agent is an actual process that runs TASK MANAGER? – galets Jul 11 '12 at 16:51
  • I'm not 100% sure. What I do know is that SQL Agent connects to the instance when it's running, and this can prevent you from logging in while the server is in single-user mode. I had that problem yesterday, and stopping SQL Agent fixed it. – Jon Seigel Jul 11 '12 at 16:58
  • 2
    Just to be clear, this isn't the same Task Manager that you use in Windows to view processes and system performance. – Aaron Bertrand Jul 11 '12 at 17:22
8

Had the same issue today, If you have not put your AUTO_UPDATE_STATISTICS ASYNC OFF, you will not be able to enter your database, you can solve this by putting your database offline. Important to know is that you need to set your deadlock priority to high, otherwise you will be deadlocked out of the command. Use following commands to get out of SINGLE user mode

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET ONLINE WITH ROLLBACK IMMEDIATE

Followed by

SET DEADLOCK_PRIORITY HIGH

ALTER DATABASE [YourDBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
  • 3
    Thank you very much for this. However I found that the OFFLINE/ONLINE step was not required; the SET MULTIUSER WITH ROLLBACK IMMEDIATE worked just by itself when SET DEADLOCK_PRIORITY HIGH was done first – Ross Presser Mar 8 '17 at 12:44
6

I think the mystery finally got resolved:

Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.

6

Stop the "system_health" extended events trace. It will be listed under

SQL Server Management Studio
-> [ServerName]
-> Management
-> Extended Events
-> Right-Click on 'System_health'
-> Hit Stop Session

Once the blocker are fixed, restart the session.

  • Although the other answer explains why this happens, this one explains how to resolve it. – Boris Callens Feb 10 '16 at 15:31
  • Perfect resolution for me. It worked as expected – Im88 Jun 28 '18 at 22:53
0

You have to disable SQL Agent 'before' starting single use mode. As the agent accout will grab the single user access. Remeber the single user is not you it the first user/process to connect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.