Day: February 12, 2013

Get VLF Counts for all Databases using Powershell

Quick script (as always) to get the vlf counts for all your databases via powershell.  Bit of a misnomer though, it just executes the sql for each database.  AFAIK, there is no mechanism in SMO to retrieve the vlf count directly.  This uses SQLPSX.  

param(
	[string]$serverName = "ServerName"
)
Import-Module SqlServer

$outputs = @();
$qry = @"
DBCC LOGINFO
"@

$server = Get-SqlServer -sqlserver $serverName
$dbs = Get-SqlDatabase -sqlserver $server | where{$_.IsSystemObject -ne $true}
foreach($db in $dbs)
{
	$res = Get-SqlData -dbname $db.name -sqlserver $server -qry $qry
	$output = New-Object -TypeName PSObject -Property @{
		DatabaseName = $db.Name
		VLFCount = $res.Count
	}
	$outputs += $output
}
$outputs | Format-Table -AutoSize

Powershell Script to drop a User from all Databases

Quick script to drop a user from all databases.  This does not drop the schema if said user owns one.  It will error if the user owns a schema.  This also uses SQLPSX.  Use at your own risk.

Import-Module SqlServer
$serverName = 'ServerName'
$loginToRemove = 'LoginOrUserName'

$dbs = Get-SqlDatabase -sqlserver $serverName | where{$_.IsSystemObject -eq $false}
foreach($db in $dbs)
{
	foreach($user in $db.Users | where{$_.Name -eq $loginToRemove})
	{
		$user.Drop();
	}
}