I’ve been trying to get used to using the dbatools cmdlets in PoSh. They help me learn some PowerShell, but they also make some things easier. As a part of my practice, I’m documenting the various items I’ve played with. This post looks at Stop-DbaProcess.

Killing Spids

At first I didn’t think much of this cmdlet, because I need to know a spid right? The time it takes me to run sp_WhoIsActive or sp_who2, find a spid, and decide to kill it is most of the time. A quick “kill xx” is easy in SSMS.

This cmdlet does more, which makes it really handy for me. I know it’s just issuing a kill in the background, but it does have some features that perhaps make this more enticing, especially during times where I might need to quiesce  a server and remove a number of users.

The first thing that comes to mind is that I can quickly kill all the users using a particular program. For example, I had a demo app with a generic connection to SQL Server. When I ran it, there were multiple connections to SQL Server. If this had been behaving badly, perhaps with a long running query, I’d have had to kill both of these to ensure I got the right one. In a demo, perhaps “kill 60” and “kill 62” is quick enough.

I could have done this, though.

Way more typing, right? Sure it is. There’s an advantage, which I could have used more than a few times in my career. I didn’t have to look up the Spid with PoSh. In various jobs where I’ve had poorly behaving applications, I’d have to get a list of processes, find the numbers, and kill each of them individually. Easy as a one-off, harder (and annoying) when you are interrupting work regularly to repeat a tedious process.

With Sop-DbaProcess, I can keep this script ready and have it kill all the connections using that program name, whether there is 1 or 100.

What’s more, I have more options to exclude particular spids or logins, limit this to databases, run across multiple hosts, etc.

Simple, easy, and useful in some situations. I wouldn’t kill a single process with this, but I’d certainly want to use this if I had to repeat myself over and over.

Give dbatools a try and see where you might start finding PoSh to be useful while administering SQL Server.

Filed under: Blog Tagged: dbatools, powershell, syndicated