0

In my production SQL Server 2016 server,i had few dummy databases created as part of migrating from SQL Server 2008. I was cleaning up all the unwanted databases and i dropped few databases.But when i tried to drop an unused database ,i get the error message "Cannot drop the database as it is currently in use". So i checked the process/session using that database. I see that spid 144 uses it and when i did DBCC INPUTBUFFER(144),i see an Event Type as Language Event.

enter image description here

I am not sure what it means.

I tried to kill the spid earlier ,but in few seconds a new session started and i am still not able to drop that database.

This particular database is not in use for atleast 6 months now and that is why i tried to KILL the process using that db,cause i was sure that it won;t affect any.

Any idea why a new session is getting created and the database is always in use.

3
  • I think the title of your question is off. Are you trying to figure out how to drop a database with active connections, or bypass reading DBCC INPUTBUFFER documentation? – Aaron Bertrand Apr 5 '19 at 20:14
  • I want to drop the database,but more importantly i want to know what that session/process is doing.That is why i gave the title like that. – user9516827 Apr 5 '19 at 20:55
  • Well, the EventInfo column has a lot more useful information for you than the EventType column, so I'm not sure why the focus on the Language Event. – Aaron Bertrand Apr 5 '19 at 21:35
2

Your session has a connection context to the database. In order to drop a database, you need to remove all connections (including your own query window & Object Explorer connections):

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

(See also this question.)

This is much cleaner and immediate than playing KILL-a-spid (whack-a-mole) for individual sessions and hoping you get your alter in before another session establishes connectivity.

We can't tell you why this session is getting created, something that is calling dtb.compat... which isn't anything built into SQL Server, so it must be a user or some application. You can get more detail when you catch this query in the act, like host name, app name, and other details, by looking at sys.dm_exec_connections, sys.dm_exec_connections, sys.dm_exec_requests, etc.

Your query against sysprocesses is problematic for two reasons. (1) dbid there is unreliable. A session can definitely hold locks against a database with a different dbid indicated there (think cross-database joins). (2) sysprocesses is deprecated:

enter image description here

As for the Language Event question, this just means that the last command that session sent to SQL Server was query text. The other option (other than No Event) is an RPC Event, which in simplest terms means a procedure call. See the DBCC INPUTBUFFER topic in the documentation for more details.

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.