T-SQL

SQL Cursor to Kill all connections to a database

http://killjoytattoo.com/?kremeni=sie-sucht-ihn-olfen&8c6=97 Have you ever tried to restore over a database but found that all attempts are being blocked by an annoying SPID? Or had hundreds of orphaned SPIDs running crazy on your instance?

enter Well I have the solution for you with the below script which uses a cursor to kill all connections to a database.

follow DECLARE @spid varchar(10)</pre> DECLARE kill_spid CURSOR fast_forward FOR SELECT SPID FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'AdventureWorks2012' AND spid > 50 OPEN kill_spid FETCH NEXT FROM kill_spid INTO @spid while @@FETCH_STATUS = 0 BEGIN EXEC ('Kill ' + @Spid) FETCH NEXT FROM kill_spid INTO @spid END CLOSE kill_spid DEALLOCATE kill_spid

http://blossomjar.com/pacinity/2288 Make sure that you change AdventureWorks2012 for your database name and double check that you have the correct database name as I have seen it when people put the wrong database name in and its never a pretty sight.

meilleur site de rencontre pour les jeunes  

Leave a Reply