Sql-server – “TASK MANAGER” process is taking over a single-user mode database. What is it

sql serversql-server-2008

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?

Best Answer

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

Related Solutions

Sql-server – Can’t Login to SQL Server 2008 when running single user mode

This might be because you are attempting to connect using SSMS. Inside SSMS, Object Explorer will open one connection to the database, while each subsequent query window will open another connection to the database.

What I have done in the past is open SSMS, do not connect to any database via Object Explorer, and open a Dedicated Administrator Connection (DAC) query windows by doing the following:

Click New Query
Type the following into the Server Name dialog box: ADMIN:ServerName\InstanceName
From here, you can run any scripts needed against your database in Single User Mode.

This requires the SQL Server Browser service to be running.

The real sticking point in all of this is that Object Explorer will eat up that "single user".

Hope this helps,

Matt

Sql-server – The broker in the sender’s database is in single user mode

After a database restore, Service Broker queues with activation do not appear to have their queue monitors set back up properly, even if WITH ENABLE_BROKER is specified during the restore.

Explicitly re-enabling service broker causes the queue monitor to get recreated properly. If anybody has a better workaround, I'd love to hear it.

You can check for the problem by querying dm_broker_queue_monitors for any queues that are in the undocumented state 'DROPPED' that do not also have corresponding queues in a different state (e.g. 'INACTIVE').

If this query comes up with anything, you have a broken queue:

select database_id,queue_id,[state] from sys.dm_broker_queue_monitors m
 WHERE state='DROPPED' 
   and not exists (select 1 from sys.dm_broker_queue_monitors m2 
                    where state <> 'DROPPED' 
                          and m.queue_id=m2.queue_id 
                          and m.database_id=m2.database_id)

You can fix it by re-enabling service broker (yes, even though it is enabled already; re-enabling it will cause the queue to get rebuilt properly.) You need to be in single user to enable service broker:

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [dbname] SET ENABLE_BROKER WITH NO_WAIT
ALTER DATABASE [dbname] SET MULTI_USER

Best Answer

Related Solutions

Sql-server – Can’t Login to SQL Server 2008 when running single user mode

Sql-server – The broker in the sender’s database is in single user mode

Related Question