Every DBA needs a few tricks up his/her sleeves to help them better manage their SQL Server 2000 environment. In this article I will explore a couple of such tricks. I will first explore how to cycle the SQL Server error log, and why you might want to do this. Secondly I will discuss backup history information and why you would need to periodically remove some of the older history information.
Cycling the ERRORLOG file
Have you ever tried to bring up the error log file and had to wait quite a while before the log is displayed? Then once the error log file was displayed you had to wade through days, weeks, or perhaps months worth of log information prior to getting to the specific timeframe that interested you? Well if you have had this problem then you might want to consider cycling that error log more frequently. By cycling the error log, I mean closing the existing log and creating a new one, without shutting down SQL Server.
The error log file can grow quite large if you leave SQL Server up and running for long periods of time, and/or you log lots of information. SQL Server creates a new error log file every time you startup SQL Server. SQL Server limits the number of old error log files that are kept, on disk, prior to being recycled (deleted). By default the number of old error log files that are kept on disk is 6. The current error log file is named ERRORLOG, while older log files have a number appended to their name, such as ERRORLOG.1, ERRORLOG.2, etc. ERRORLOG.1 is the most current old log, ERRORLOG.2 the next most current log, etc. When a new error log file is created and there are as many old error log files on disk as allowed by the server configuration, then the oldest log file is deleted when a new ERRORLOG file is created.
SQL Server provides a system stored procedure (SP) to cycle the error log. This SP is called sp_cycle_errorlog. When this SP is executed it will create a new log file and rename the existing error log file to ERRORLOG.1, and then rename ERRORLOG.1 to ERRORLOG.2, and so on. If the maximum number of error log files allowed by SQL Server already exists, then the oldest error log file will be deleted.
If you plan to cycle the error log file using the sp_cycle_errorlog SP, keep in mind the number of old error logs that SQL Server will retain, and the timeframe for the log entries that will be contained in those old logs. You will need to determine how far back in time you might want to review information in the old error log files and then determine the frequency for cycling the error log so you will always have the data in the error log files for the dates in which you think you might need to review some day.
If you find you need to cycle the error log frequently, due to its size, then you might need to keep more than 6 old logs. To do this you will need to change SQL Servers default setting for how many old error logs are kept. To do this in SQL Server 7.0 you will need to modify the registry. The following knowledgebase article explains how to do this: http://support.microsoft.com/?kbid=196909. In SQL Server 2000 and 2005 changing the default number of error logs to be kept is done via a context sensitive popup menu. In SQL Server 2000 you do this by expanding the Management folder in Enterprise Manager, than right click on the SQL Server Logs item and finally clicking on the Configure item. Doing this will bring up a window similar to below:
In SQL Server 2005 you use SQL Server Management Studio to change the number of default logs to be kept. You do this by expanding the Management folder in the Object Explorer, or Summary view, and then right clicking on the SQL Server Logs item, then when the menu is displayed click on the Configure item. Doing so will bring up a window similar to below:
As you can see the SQL Server 2000 and 2005 methods and windows to change the number of old logs to retain is quite similar. On these screens you just need to click the check box, and then enter the number of logs to retain that would be appropriate for your situation.
Deleting Backup History Information
Each time you create a new database backup some information about the backup is stored in a series of backup tables within the msdb database. Over time this backup information can take up a fair amount of disk space on your server. The amount of space used will be directly related to the number of databases your server houses, plus how often you run database backups of those databases.
Have you ever used Enterprise Manager to restore a database, and noticed it takes quite a few minutes just to bring up the screen that displays the list of backups taken? If so, do you know why this takes so long? The reason it takes so long is because Enterprise Manager is querying the backup history tables in the msdb database. Enterprise Manager uses this backup information to build the window that lists the backups taken. The more information in the backup tables, the longer it takes for Enterprise Manager to read the information in the backup tables within the msdb database before displaying the list of backups to restore from.
You can save disk space and speed up the time it takes to display the Restore database pane in Enterprise Manager by only retaining the backup information for a short period of time. I have to wonder how many DBAs really need to retain the backup information in the msdb database beyond more than a few months. I am not saying DBAs dont need backup files beyond more than a couple of months, what I am saying is I dont think that DBAs will be scrolling through the list of backups taken using Enterprise Manager to restore a database from a backup file that is really old. I am guessing most DBAs only restore the most recent database backups, which are currently on disk, using Enterprise Manager, when using the information retrieved from the msdb database. If this is the case, why keep the backup information around if you are not going to use it.
SQL Server provides a SP named sp_delete_backuphistory that removes old backup information. Here is the syntax for calling this SP:
sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'
The single parameter, @oldest_date, identifies the oldest date for which backup information will be kept. This SP expects a valid DATETIME value to be passed as a parameter. Here is one example of how to execute this SP:
This example would delete all backup information in the msdb tables that is older than December 1, 2005, 12:00 AM.
Keeping the ERRORLOG file to a reasonable size makes it easier and quicker to browse. If you have large ERRORLOG files then you might want to consider periodically creating a new ERRORLOG file using the sp_cycle_errorlog SP.
When the backup tables in the msdb database retains information about a large number of backups, then this causes some slowdowns when trying to restore a database using Enterprise Manager. If there is no reason to keep your backup information beyond a certain point then you should consider deleting the backup information by using the sp_detelebackuphistory SP. Keep in mind anything you can do to speed up the work you need to do is worth doing.