Find all the sessions running in one database

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.

The server principal ‘’ already exists – MSG 15025

Not able to create SQL LOGIN even though same does not exist in the server

Today I got request from one user that she is trying to access the data source (SQL server) from EXCEL and not able to connect. She was using her windows AD account to connect to the source.

She asked me to provide the required permissions and I found she is not added in SQL login list. It is very simple, just create login and map with required databases and that’s it. But this did not work as expected and below is what I observed and experienced.

When try to create new login in required SQL server, I got following error with error number 15025.

“The server principal ‘<AD Account>’ already exists – MSG 15025”

But when I see list of logins, this login is not present at all in the server. Immediately realized of possibilities that this user AD account has been changed recently and previously she was having access to the server with different AD account.

How I verified:

Execute the query below to see if the SID already exists for this AD account.

select suser_sid ('<AD Account>')

I was surprised after seeing associated SID for this account. So I executed below query to check the actual account for which this SID was associated.

select * from sys.server_principals where SID = <SID_without_quotes>

I got the actual account name and this was exactly her old AD account. Basically login already exists on the SQL server with the same SID of the new login which I was trying to create.

I contacted user and inform that we need to drop your old account in SQL login and create the new one and then only we will be able to provide you the requested permissions. After getting confirmation, I dropped the login and created new one with updated AD account name and then user was able to access the data source without any issues.

Just wanted to share the experience so that all my friends can understand this.

Hope this helps to all of my friends.

Kill all the sessions of a database at one time

I was working with my one of junior database administrator. He was working on one of the restore in stage environment. Request was to restore stage from latest backup of prod so that build can be tested before moving to prod.

When he tried restoring the database, he got the error ‘Database is in use’ and he started execution sp_who2 ‘Active’ and started killing the sessions one by one. After killing all the sessions, he tried database restore again and it failed. Again he repeated the same process to kill the sessions one by one. He did it but it was pain for him again and again doing this.

Below is the script I have come up with which can be used to kill all the sessions at one time. Just change the database name and execute it. It will kill all the sessions at one time and you will be able to run your restore with immediate effect.

USE master

GO

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM sys.databases WHERE name = '<DB_Name>')

      BEGIN

      DECLARE     @spid int,

                  @sql nvarchar(max) ;

      DECLARE curSpid CURSOR FOR

      SELECT spid FROM sys.sysprocesses WHERE db_name(dbid) = '<DB_Name>' ;

      OPEN curSpid ;

      FETCH NEXT FROM curSpid INTO @spid ;

      IF @@FETCH_STATUS = 0

            BEGIN

            PRINT 'Killing open connections to database <DB_Name>.';

            END

      WHILE @@FETCH_STATUS = 0

            BEGIN      

            SET @sql = N'KILL ' + CAST(@spid AS nvarchar(32)) + N';'

            EXEC sp_executesql @sql;

            FETCH NEXT FROM curSpid INTO @spid ;

            END

      END

GO

close curSpid

deallocate  curSpid

go

Hope this helps to all of my friends.