Document your SQL Agent Jobs

And I don’t mean writing it down in a word document, leaving it somewhere on the network, and then forgetting about it. How about keeping the documentation with the job? Microsoft provides us with a space for it…

In this description field ideally I’d like to see

1. A brief description of what the job does.
2. Who owns the job. Who can I bother if there’s something I don’t get?
3. How critical is this job? Can it wait until you’re back from holiday next week or does it have to be fixed as soon as feasible?
4. Links to further documentation. I don’t expect an essay in here so include detailed documentation elsewhere and provide links if needed.

This little bit of Powershell will check SQL Agent jobs on multiple servers for a description. Any jobs without a description will be highlighted in red.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

# Array containing sql server names
$servers = @("localhost");

# Process each sql server
foreach($server in $servers)
{
	Write-Host "$server ";
	Write-Host "===============================";
	# Create a SMO server object
	$srv = New-Object Microsoft.SqlServer.Management.SMO.Server $server;
	$jobs = $srv.JobServer.Jobs;
	foreach($job in $jobs)
	{
		$jobName = $job.Name;
		$jobDescription = $job.Description;
		$colour = "White";
		# If job is not documented highligh in red
		if($jobDescription -eq "No description available.")
		{
			$colour = "Red";
		}
	Write-Host -ForegroundColor $colour $jobName $JobDescription;
	}
	Write-Host "";
}

So get documenting those jobs and you might be left in peace on your next holiday!

If you liked this you might also like;

Documenting Databases with Powershell

Extract Stored Procedure comments with TSQL

System Documentation: My Method