T-SQL Tuesday (aka #TSQL2sday) is a monthly SQL Server blogger event started back in late 2009 by Adam Machanic (blog | twitter). For more info on its beginning and purpose see the origin of TSQL2sday. Each month a different SQL Server blogger is the host (announces the theme and compiles a recap). This month’s event is hosted by Andy Yun (blog | twitter) and the selected theme for this month is “Just Say No to Defaults”.
This is really embarrassing, but I’ve had a blog post started for this topic for years and somehow never got around to finishing it! Thanks, Andy, for giving me a reason to finally address a couple of my “must change” defaults.
Ease of installation is definitely a feature that has helped SQL Server to proliferate. You can have a functional system just by running setup and clicking Next, Next, Next….Finish! Without having to make any real decisions about what you are doing, you can be up and running in no time.
When installing the database engine component, the first change to be considered from the defaults presented during setup is the location of the various file types – however, I’m going to save that for others to address and may come back to it in a future post.
Today, I’m going to address a default that you can’t change during the setup dialog or via configuration file parameters. It must be adjusted post-install and is for SQLAgent.
Ever go to review the job history for a job and find nothing or only a couple of recent entries and you know the job has been running for weeks or even months? So, where is all that history? Sorry, you were the victim of the ridiculous defaults shown below which limit the total number of rows in the msdb.dbo.sysjobhistory table as well as set a max number of history rows per job.
To find this dialog in SSMS, right-click on SQLAgent, then select Properties, then select History.
These are defaults that you definitely want to change. In fact, instead of just increasing the number of maximum rows for the table and per job, I’d recommend that you decide on the time frame that you want to keep your SQLAgent job history and uncheck the “Limit size of job history log” option and check the “Remove agent history” option and specify the desired time frame instead as shown below. Many companies already have specifications for how long to retain activity logs, so using the time period that meets or exceeds those requirements should be helpful when it comes audit time.
Depending on the number of jobs and the frequency at which each is run, you may also need to keep a close watch on the size of msdb after changing this setting to find the optimum size for your msdb files to allow the sysjobhistory table to grow to its expected size without causing autogrow of your files. Remember to manage msdb just like any other application database with appropriate purges, backups, and other maintenance.
I can’t wait to see what others say “no” to in Andy’s round-up for this event. I’ll be looking for my other must change items and if I don’t see them, then I will be posting more soon!