Really Long Descriptions on SQL Agent Jobs

WARNING: Silent truncation ahead

Have you ever tried to enter a really long description on a SQL Agent job?

Maybe you were trying to put some sort of warning to people who might be trying to edit the job, or triage info for when the job fails. (Ok, so this sort of documentation should be on your wiki, not in a job description… but stuff happens)

Thankfully, the Job Properties window has this really helpful text box that takes a seemingly unlimited (actually 32,767 characters) amount of text. So type away! (Tip: use CTRL+Enter to add a carriage return) In this example, I entered 600 characters of text (conveniently with numbers throughout showing the length):

You save your documentation, you go back later, and…wait…where did it all go?

SQL Server helpfully truncated the text to 512 characters. What the hell? Hopefully you have your documentation saved elsewhere.

Take a look at the sysjobs table in msdb. The description column is nvarchar(512). SSMS allows you to enter more then silently truncates to fit the database. Super helpful!

CREATE TABLE [dbo].[sysjobs](
 [job_id] [uniqueidentifier] NOT NULL,
 [originating_server_id] [int] NOT NULL,
 [name] [sysname] NOT NULL,
 [enabled] [tinyint] NOT NULL,
 [description] [nvarchar](512) NULL,
 [start_step_id] [int] NOT NULL,
 [category_id] [int] NOT NULL,
 [owner_sid] [varbinary](85) NOT NULL,
 [notify_level_eventlog] [int] NOT NULL,
 [notify_level_email] [int] NOT NULL,
 [notify_level_netsend] [int] NOT NULL,
 [notify_level_page] [int] NOT NULL,
 [notify_email_operator_id] [int] NOT NULL,
 [notify_netsend_operator_id] [int] NOT NULL,
 [notify_page_operator_id] [int] NOT NULL,
 [delete_level] [int] NOT NULL,
 [date_created] [datetime] NOT NULL,
 [date_modified] [datetime] NOT NULL,
 [version_number] [int] NOT NULL
) ON [PRIMARY]

But wait… there’s more!

Are you using Multi-Server jobs? When you deploy a job to a TSX, there’s even LESS of the description that gets deployed. The description is silently truncated to 255 characters.

The moral of the story is Don’t document your jobs in the job description. It’s not the best idea, and you’ll just end up shooting yourself in the foot. Instead, document your job in your wiki or knowledge base or wherever you keep your standard documentation. Then put the URL to the documentation in the job description.