#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.

Share on: