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…

sql_agent_job_description_field

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.

?View Code POWERSHELL
[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 "";
}

powershell_sql_agent_job_description

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


2 Comments

  1. Emily says:

    Do you know how many characters are allowed in the description field by any chance? It doesn’t seem to be documented online anywhere.

    I know you can get around the line break limitation by copy-pasting a line break (or editing it in a T-SQL script), and I’ve been using it as a change history log for multi-server jobs. The description often gets truncated once it’s pushed out to the target servers though. I was seeing a ton of mysterious “string data would be truncated…”s in sqlagent.out and took a while to figure out where it was coming from.. 🙂

  2. Emily says:

    Oh Nevermind – nvarchar(512) according to http://msdn.microsoft.com/en-us/library/ms189817.aspx. Hmm.

Leave a Reply