Get Active Session per database

Standard

Sometimes we have the issue that a database is locked due the fact that someone is using the database for some apparent reason.

The same happened to me when I wanted to restore a database but failed because someone was using it.I wanted a script that showed me the sessions for a specific database and the login name.

The code below helps you with that:

USE master
GO
SELECT
db_name(dbid) as DatabaseName
, loginame as Username
FROM sys.sysprocesses
WHERE dbid > 0
AND db_name(dbid) = '[DATABASENAME]'
GROUP BY dbid, loginame
GO

To see all the connections for a;; databases you can use the following script:

USE master
GO
SELECT
db_name(dbid) as DatabaseName
,count(dbid) as Amount
, loginame as Username
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
GO

Leave a Reply

Your email address will not be published.