Many a times, we come across the situations like immediate refresh of Dev or QA or Unit database with that of Production backup.
In that situation, despite many warnings, some connections always exist. Manually killing each and every process connecting to the database is not a reliable solution. Its tiresome,time-taking and as soon as we finish killing all the process we find new processes.
In that case, the following procedure can come to the rescue of a DBA.
USE MASTER
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'KillProcess' AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE dbo.KillProcess
GO
CREATE PROCEDURE dbo.KillProcess(@dbname VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON
DECLARE @process TABLE (PId INT)
DECLARE @pid INT, @qry NVARCHAR(15)
INSERT @process
SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@dbname)
SET @pid = (SELECT TOP 1 PId FROM @process )
WHILE @pid IS NOT NULL
BEGIN
SET @qry = 'KILL '+CAST(@pid AS VARCHAR)
--PRINT @qry
EXEC SP_EXECUTESQL @qry
DELETE FROM @process WHERE PId = @pid
SET @pid = (SELECT TOP 1 PId FROM @process )
END
END
-- EXEC KillProcess 'TestDev'
GO
This procedure kills all the process that exist and make the database ready to get refreshed for a moment.
