DBCC INPUTBUFFER shows Event Type as Language Event for an unused Database

Question

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.

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.

Answer 1

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:

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.