Parv The IT Geek

Join me on my Geeky Adventure

  • Home
  • About Me
  • Contact

Powered by Genesis

SQL Cursor to Kill all connections to a database

27/03/2015 by Parvinder Nijjar Leave a Comment

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?

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

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

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.

 

Share this:

  • Click to print (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email this to a friend (Opens in new window)

Related

Filed Under: T-SQL Tagged With: Database Administration, Programming, T-SQL

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Quick introduction to SQL, databases and relational theory
  • My top tips for new programmers
  • Book Review: Learn Windows PowerShell in a Month of Lunches, Second Edition by Don Jones
  • Why you should learn programming
  • What would you like to learn or for me to talk about?

Categories

  • Analysis Services
  • C#
  • Database Engine
  • DMV
  • General
  • Integration Services
  • Personal Development
  • PowerShell
  • Replication
  • Reporting Services
  • T-SQL
  • Uncategorized

Tags

Attach database basics Business Intelligence C# Database Administration Database Design Database Engine DBCC CHECKDB DMV Export Data Intro Monthly Theme Personal Development PowerShell Programming Replication Script SSAS SSIS SSRS SSRS Administration T-SQL Windows Administration
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.