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.
No comments:
Post a Comment