It’s Two For One day here at shaunjstuart.com! That’s right! Today only, you get TWO tips for the price of one!

One of the things I routinely do when setting up a new SQL Server or adding one to the flock that I already manage is to set up a job to maintain the job history maintain in the msdb database. If this isn’t done, the msdb database can grow very large, especially on servers that have been running for years and are taking frequent backups. the majority of the time, you don’t need all that job execution history, so I use the sp_delete_backuphistory to keep the size under control.

I recently discovered another thing that can bloat msdb – sendmail logs. With SQL 2005, it became much easier to send email from SQL Server. Database mail is a handy little utility for sending email and I use it to send all sorts of alerts about the state of my servers. In my environment, the developers have also used it to send out email from SSIS packages. We have hundreds of these that run on schedules from weekly to daily to hourly. I think it’s safe to say my SQL Servers send out more email than the combined top 25% of our users.

When Database Mail sends a message, it stores a copy of the message in a table in the msdb database, as well as any attachments the email might have. It will also create a log entry in msdb regarding the sending of the message. If your server sends out a large number of emails, you may find the size of you msdb database becoming quite large. Microsoft has provided two stored procedures to help maintain these tables: sysmail_delete_mailitems_sp and sysmail_delete_log_sp. The commands have a couple of different parameters, but the most common way to use them is to supply a date for which all emails or log records prior will be deleted. Check with your business to see what your retention requirements are and then set up a job to perform this maintenance periodically. If you haven’t been doing this, you can realize some significant space savings. On one of my more active servers, I freed up 250 MB of space from msdb. That translates to faster backups, restores, DBCCs, etc.

It was while I was setting this maintenance task up across my servers when I ran into a little problem that led me to my second tip. I created this job originally on a SQL 2008 R2 server and then used SSMS to script it out. I ran the script on other 2008 R2 servers without problem, but when I ran it on a SQL 2005 server, it failed with an error: @schedule_uid is not a parameter for procedure sp_add_jobschedule.

A quick visit to Google showed me the problem and the solution. SSMS can be configured to create scripts using different versions of T-SQL. I was using SSMS that came with SQL 2008 R2, so that was the T-SQL version it defaulted to. But if you go to Tools –> Options –> SQL Server Object Explorer –> Scripting, you can select a different version:

When I switched to script for SQL Server 2005, I got a new script that ran without errors on both my 2005 and 2008 R2 servers.

So what was the difference that was causing me problems? It had to do with the scripting of the job schedule. Here is the script in 2005 T-SQL:

-- 2005

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
		@job_id=@jobId,
		@name=N'Sa @ 5 AM',
		@enabled=1,
		@freq_type=8,
		@freq_interval=64,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20120430,
		@active_end_date=99991231,
		@active_start_time=50000,
		@active_end_time=235959

And here is it in 2008 R2 T-SQL:

-- 2008

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
		@job_id=@jobId,
		@name=N'Sa 5 AM',
		@enabled=1,
		@freq_type=8,
		@freq_interval=64,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20120430,
		@active_end_date=99991231,
		@active_start_time=50000,
		@active_end_time=235959,
		@schedule_uid=N'0623b9e9-fd03-4dcb-866d-cb156c623261'

Note the last line – the @schedule_uid parameter is not supported in SQL 2005. This is an output parameter in 2008, 2008 R2, and 2012. It doesn’t appear to be well documented in MSDN. In fact, for the MSDN entries, it’s not even listed in the Syntax section at the start of the entry, but you only find it as the last entry in the Arguments section. (There are a couple of Connect items filed regarding this, but Microsoft seems to have closed them all as “By Design.” Even granting them the design part, I think this is a pretty poor design. At the very least, @schedule_id is also an output parameter and that is not scripted at all in any version of T-SQL. So why is just @schedule_uid scripted?)