Process won't die

While troubleshooting a problem with a web app having intermittent problems, I'm looking at the database as the cause. I made a backup of the database. Afterward I noticed a process in Activity Monitor using that database with 2 open transactions and a green check icon that won't kill (it says "Cannot use KILL to kill your own process. error 6104"); this process was not present prior to the backup. The process User shows my login (using Windows authentication and my domain account or while logged in as SA) and the Application is SQL Server Mgmt Studio. The host is the server name. Status is runnable.

I stopped and restarted SQL Server and SQL Server Agent using the Windows Services Console, not the SAC if it makes a difference. I also detached and reattached the database; the process still lives with 2 Open Transactions and is still using the same database. The text of the details doesn't change at all, which is:

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(61)')
select [Event Info] from #tmpDBCCinputbuffer

I don't know if this has anything to do with the original problem, but I don't think it is helping either. Can someone tell me what the details mean and how to kill the unkillable process?

Thanks!

I think you'll find this to be a bit of a red herring.

In Activity Monitor does the name in the 'User' column for this particular row contain the name of the login that you use to connect to the SQL Server instance in Management Studio? If so then there's nothing to worry about as the row represents Management Studio's connection to the SQL Server instance. You should also find that the default database of the login that you are using is set to the name of the database that is displayed in the row.

The DBCC INPUTBUFFER(spid) command will return the text of the most recent batch executed against a specified SPID. When you double-click on a row in Activity Monitor (or right-click and select 'Details') then, under the covers, SQL Server runs the command you quoted in your post above (substituting the SPID of the selected row - 61 in your example) to return the most recent batch issued against the connection. In your example the batch actually happens to be the CREATE TABLE etc... batch that was issued by Activity Monitor to return the most recent batch!

In short, there's no need to worry about this connection - it is perfectly normal to see it when Management Studio is open and it is unlikely to be causing any performance problems.

Chris

|||

The name is always the login used for Mgmt Studio. My login's default db is master, but the activity monitor shows the db in question, even when I set to use another db and run a query against it. I'm curious why I hadn't ever seen it before. Are there certain conditions that will make it appear or not appear?

I think I figured out the original problem, but I'm still curious about this process. I stopped and restarted the service. The first Activity Monitor refresh immediately after doing so showed the db as master, refresh about 5 seconds later the db showed tempdb... then a couple more refreshes later showed the db in question, all without me actually running any commands. Ten minutes later it is still showing the db in question.