I have observed so many times that most of
the junior DBAs use SP_WHO2 ‘Active’ command to find the sessions running in
the server against a database. They do when there is request to restore/refresh
one database. It does not make sense to me. If you have 100 of databases and
you have to restore only one of them, it’s difficult to find actual session
running against that database by using SP_WHO2 ‘Active’.
Below is what I use and this gives me better
option to understand the query/process running against database. Just we need
to enter database name and we can see all the sessions running.
SELECT * FROM SYS.SYSPROCESSES WHERE DB_NAME(DBID) = '<DatabaseName_Here>'
Go ahead and
kill the session if you want to restore database and avoid the error ‘Database
is in use and cannot obtain exclusive lock’.
Hope this helps to all of my friends.