Sometimes you need to kill processes in SQL. I find that when I need to restore a database in I get that annoying “Cannot get exclusive access” error. One way to do it would be to run the command;
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
I find this a little harsh as I can do some real damage in a hurry if I muck it up. Not only the database I am dealing with but the whole server.
I prefer just killing or the processes relating to the database and then working on it immediately afterward.
Killing the processes in the GUI can become a little tedious though so I wrote a quick script (below) to do the leg work for me.
Now if a want to kill all processes relating to a database I simply pass the database name into my script (without passing a user name) and bang, they are all dead.
I ended up adding the login parameter because although I have not yet used it I can think of reasons in the future where it may come in handy. Say for instance, a rogue user is hogging all the server resources I can knock them out in a single punch. Obviously I am an admin though.
To see which login is using the most resources directly from TSQL use this query (SQL 2005,2008+);
SELECT pr.loginame
, COUNT(*) Processes
, SUM(pr.cpu) cpu
, SUM(pr.physical_io) physical_io
, SUM(pr.memusage) memusage
FROM master..sysprocesses pr
GROUP BY pr.loginame
ORDER BY SUM(pr.cpu) DESC, pr.loginame
The script (SQL 2005,2008+);
–KILL SQL PROCESSES
–proficientit.com.au
–Note passing nothing kills all processes except the current one.
–Passing only @dbName name will kill all processes for that database.
–Passing only @loginName name will kill all processes for that login.
DECLARE @dbName As Nvarchar(50) = 'DB_NAME' –Database name
DECLARE @loginName AS Nvarchar(50) = 'MY_DOMAIN\MY_USER' –'MY_DOMAIN\MY_USER' or 'SQL_USER', basically how it appears in Security/Logins
USE master
IF @dbName IN('master','model','msdb','tempdb') BEGIN RAISERROR('This script can only kill user processes.',16,1) RETURN END
IF ISNULL(@dbName,'') <> '' AND DB_ID(@dbName) IS NULL BEGIN RAISERROR('Database does not exist or you don''t have permission to see it.',16,1) RETURN END
IF ISNULL(@loginName,'') <> '' AND NOT EXISTS(SELECT name FROM master.dbo.syslogins WHEREname = @loginName) BEGIN RAISERROR('Login doesn''t exist on this server or you don''t have permission to see it.',16,1) RETURN END
DECLARE @spid INT
DECLARE @sql Nvarchar(100)
DECLARE csurProcess CURSOR FOR
SELECT pr.SPID
FROM master..sysprocesses pr
INNER JOIN sys.databases da ON pr.dbid = da.database_id
WHERE (da.name = @dbName OR ISNULL(@dbName,'') = '')
AND (pr.loginame = @loginName OR ISNULL(@loginName,'') = '')
AND pr.SPID <> @@SPID –Can't kill this session(this script is running in it).
OPEN csurProcess
FETCH NEXT FROM csurProcess INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'Killing process ' + CONVERT(NVARCHAR, @spid) + '.'
SET @sql = 'KILL ' + CONVERT(NVARCHAR, @spid)
EXEC(@sql)
FETCH NEXT FROM csurProcess INTO @spid
END
CLOSE csurProcess
DEALLOCATE csurProcess
–See what processes are current active.
SELECT pr.SPID, da.name [dbName], pr.loginame
, pr.cpu, pr.physical_io, pr.memusage, pr.login_time, pr.last_batch
FROM master..sysprocesses pr
INNER JOIN sys.databases da ON pr.dbid = da.database_id
Enjoy.
Related Posts: