SQL Server Job History

In running Varonis DatAdvantage there are times when you want to look at the history of the nightly jobs for a longer period then the defaults provided by SQL Server 2005. These defaults are based on 'Maximum job history log size (rows)' and 'Maximum job history rows per job.' If you are monitoring a large number of servers than the system may only keep several days worth of history for each job. Where disk space on the SQL Server is not an issue one change the the delete option to purge data based on an overall duration, which can be specified in days, weeks or months. For example, we might want to retain 10 days worht of history to assist in debugging issues. To do that perform the following steps.


First run SQL Server Management Studio.

Then navigate to:
• Root
• SQL Server Instance
• SQL Server Agent
• Right click on SQL Server Agent

• From here right-click on history.
• Select the option to "Automatically remove agent history" and enter the duration that you want to keep the job history.
• Click on OK and you are ready to run.
  

SQL Server 2005 on Windows Server 2008

If you want to install SQL Server 2005 with Reporting Services on Windows Server 2008 you have to jump through a few hoops. Reporting Services is dependent on IIS 6 and SQL Server 2008 runs IIS 7. However, there is the capability to emulate II6, which is critical to making this work.

There is a great blog post on this issue at iGregor, where he walks you through the exact configuration options to make this work.

Hope this helps all those who see that grayed out Reporting Services box in SQL Server 2005 install and are shaking their heads.

Renaming SQL Backup Files

If you use a SQL 2005 Maintenance Plan to create backups of individual databases, the .bak files have a date stamp on them. I have a customer that wants to handle the files with an automated tool and would prefer that the backup files have a consistent name. We could backup up the databases en masse, but we wanted separate backups for this purpose.


The solution was to create a VB script that runs as a scheduled task and renames the files every night after the backups are run. Here is the code that I wrote to handle renaming all of the files.

-------------------------------

------------------------------------------

Note: this will fail in 2100. Just setting up some Y2100 work for your grandchildren.