QuickBlog: Using PowerShell to Kill Databases Processes in SQL Server…

This is going to be fast.  I just got tired of doing the following step on my SSMS (SQL Server Management Studio):

1. Click on SQL Server Activity Monitor.

2. Expand “Processes”.

3. Click on the “Database” column.

4. In the “Database” column drop-down list, look for my database to kill all the connections.

5. Here’s the fun part…  I have to right-click and select “Kill Process” on each of my database I want to be clear.

Well, I finally got PowerShell to get it done quickly for me.  With a simple using 2 lines of code as long as you are using SQLPS.exe and/or loading the SQL Server 2008 provider and snapins.  Here’s how:

$SQlSvr1 = New-Object Microsoft.SqlServer.Management.Smo.Server "YourServername_or_w/InstanceName"
$SQlSvr1.KillAllprocesses('dbName')

 

If you don’t have SQL Server 2008 installed, then you could use SMO to accomplished the same thing but, unfortunately, you have to add one move line of code:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$SQlSvr1 = New-Object Microsoft.SqlServer.Management.Smo.Server "mtrinidad-01"
$SQlSvr1.KillAllprocesses('developer')

 

Now, script is a very simple script, and you can explore what other objects you use from “$SQLSvr1” variable.  Make sure be familiar and use Get-Member cmdlet. “$VariableYouJustCreated | Get-Member” – to display all the methods and properties from that object.

Sample Get-member result…  by the way you can use the alias and us “gm”.

Enjoy!!

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to QuickBlog: Using PowerShell to Kill Databases Processes in SQL Server…

  1. Anonymous says:

    Awesome thanks, that helped me out!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s