Day: October 23, 2011

Find Servers with large sysjobhistory tables

This script will return you a list of servers with the rowcount of sysjobhistory >=500.  Much like the previous post, used for finding servers that have no history cleanup jobs.  You will need SQLPSX installed to run this.

cls
$servers = Get-Content c:\Test\Servers.txt
$countThreshold = 500

foreach($server in $servers)
{
	$rows = Get-SqlData -dbname msdb -sqlserver $server -qry "SELECT COUNT(*) AS HistCount FROM dbo.sysjobhistory" #| Format-Table -AutoSize
	$nRowCount = [int]$rows[0]
	if($nRowCount -ge $countThreshold)
	{
		Write-Host "Server $server has a rowcount of $nRowCount in sysjobhistory.  There is probably no history cleanup job on this server."
	}
}

Find Servers with No History Cleanup

Quick powershell script to show servers that don’t have a history cleanup job.  This script looks for jobs with the name ‘clean’ and ‘hist’ without ‘distribution’ in the name (to save false positives from the ‘Distribution clean up: distribution’ job, if your server is a distributor).  I’m sure there is a better way of checking for this (like checking the count of the msdb.dbo.sysjobhistory table), but this should work just as well (as long as your job is named aptly). 

You’ll need SQLPSX installed to run this.  Usual warnings, run at your own risk.

cls
$servers = Get-Content c:\Test\Servers.txt

foreach($server in $servers)
{
	$hashist = $false
	$jobs = Get-AgentJob $server
	foreach($job in $jobs)
	{
		#Write-Host $job.Name
		if($job.Name -like "*clean*" -and $job.Name -like "*hist*" -and $job.Name -notlike "*distribution*")
		{
			$hashist = $true;
			break;
		}
	}
	if($hashist -eq $false)
	{
		Write-Host "Server $server does not have a history clean up job"
	}
}