Monthly Archives: August 2012

Superfast Powershell Script Gets SQL Server Log Utilization

This is a nifty Powershell script which uses DBCC sqlperf(logspace) to get the log utilization for all your databases on all your servers. Best of all, it is super quick! I have over a dozen servers from 2000 to 20008 with numerous databases on each. This routine returns all my log information in a matter of seconds! It is also an example of developing your own Powershell for piping objects in your scripts. This little endeavor has taught me the real value of returning objects from the “GETS” I develop.

Measure-Command determined the collection process for ALL my servers took less than a second!

Measure_GetLogSpace

The code is presented in several blocks because of the modular design whereby I can possibly use some of the pieces in other ways. The functions are imbedded in a PowerShell module which has many other SQL helper functions not pertinent to this topic. Take a look at An Introduction to PowerShell Modules which is a really good start for developing and using modules in version 2. Using modules to help centralize my admin functions has been of enormous benefit especially when developing new scripts. I like putting my helper functions into a module because my PowerGUI editor is able to pick them up for intellisense. Love that PowerGUI! Also note: everything presented is dependent on at least Powershell V 2.

This first block is the main function which does the actual work of getting and delivering the log information. You will notice the use of Begin… Process… and End… blocks and  a parameter which allows pipeline input.

Function Get-LogSpaceUsed {
<#
.SYNOPSIS
Gets Log space utilization for all databases on an SQL Server instance.

.DESCRIPTION
Uses DBCC sqlperf(logspace) to get log space utilization for all instance databases
for one or multiple servers. Server names can be piped to this function. Output is 
an object or array of objects with ServerName, DatabaseName, PercentLogSpaceUsed,
and LogSizeMb.

.PARAMETER Server
Mandatory: The SQL Server instance which is to be evaluated. Accepts pipeline input.

.EXAMPLE
Get-LogSpaceUsed -Server 'Server\Instance'
Returns log utilization for all databases on an instance.

.EXAMPLE
Get-Content "C:\Scripts\PoSH\Servers.txt" | Get-LogSpaceUsed 
Pipes a list of server names to the function and returns log utilization for
all databases on each server in the list.

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	[CmdletBinding()]
	Param(
	[Parameter(Position=0, Mandatory=$true,ValueFromPipeline = $true)] 
	[string]$server
	)

	Begin {
		$alldata = @()
		$query = @"
DECLARE 
	@dbcc varchar(250),
	@scom varchar(250)
	set @dbcc = 'dbcc sqlperf(logspace)'

Create Table #Logspace 
	(
		DatabaseName varchar(250),
		LogSizeMB dec(10,2),
		PercentLogSpaceUsed dec(5,2),
		[Status] int
	)

Insert Into #LogSpace
	EXEC (@dbcc)

SELECT @@SERVERNAME As 'ServerName', DatabaseName, PercentLogSpaceUsed, LogSizeMB 
FROM #LogSpace 
ORDER BY LogSizeMB desc

Drop Table #LogSpace 
"@
	}

	Process{
		Try {
			# Get a new connection
			$conn = New-SqlConnection -server $server
			$ds=New-Object system.Data.DataSet
			$da=New-Object system.Data.SqlClient.SqlDataAdapter($query,$conn)
			$da.fill($ds) | Out-Null
			$da.Dispose()
		}
		Catch { 
			Throw $_.Exception 
		}
		Finally {
			Close-SqlConnection -conn $conn
			$conn = $null

		}
		Foreach($row in $ds.Tables[0].Rows) {
			$alldata += $row
		}
	}
	End {
		Return $alldata
	}
}

A helper function to create a new SQL Connection suitable for running a query

Function New-SqlConnection {
	[CmdletBinding()]
	Param(
		[string]$server=$(Throw "Missing server param"),
		[string]$database="master"
	)
	Process{
		Try {
		    $conn = new-object ('System.Data.SqlClient.SqlConnection')
		    $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name='Powershell App'"
		    $conn.ConnectionString = $connString
		    $conn.StatisticsEnabled = $true
		    $conn.Open()
			$conn
		}
		Catch {Throw $_.Exception }
	}
}

AND….. the script to bring it together and deliver the results

<#
.SYNOPSIS
Gets Log space utilization for all databases on one or more SQL Server instance(s).

.DESCRIPTION
Pipes an array of server names to a module funtion which obtains log utilization
information for each database and presents the rusults

.PARAMETER Servers
Optional: The SQL Server instance which is to be evaluated. If a server or list 
of servers is not presented, the script will use a default list of servers from
a local file.

.EXAMPLE
Get-LogSpaceUsed 
Uses a default servers list from a local file to gather log information 

.EXAMPLE
Get-LogSpaceUsed -servers "Myserver1","myserver2\instance"
Gets log information for databases on two specific servers 

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	
[CmdletBinding()]
Param(
	[Parameter(Position=0, Mandatory=$false)] 
	[string[]]$servers = $(%{Get-Content "C:\Scripts\PoSH\Servers.txt"}),
	[Parameter(Position=1, Mandatory=$false)] 
	[int]$maxlogsize = 100
)
$rows = @()
Try{
	$logs = $servers | Get-LogSpaceUsed | Where-Object {$_.LogSizeMb -ge $maxlogsize}
}
Catch {
	Write-Error "$server Server error:"
	$ex = $_.Exception
	Write-Error $ex.Message
	While($ex.InnerException) {
		$ex = $ex.InnerException
		Write-Error $ex.Message
	}
}
$logs