#PSTip Get Active Database Connections of a SQL Database

Note: This tip requires PowerShell 2.0 or above.

As a part of SQL SMO automation, when we want to detach or delete databases, we might want to first drop all active database connections. But how do we even know whether there are any active connections to the database or not?

We can use the Server SMO and the GetActiveDBConnectionCount() method for retrieving the connection count.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$smo.GetActiveDBConnectionCount('MyDB')

This code snippet returns an integer number indicating the active DB connections.

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts

%d bloggers like this: