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 don’t need backup files beyond more than a
couple of months, what I am saying is I don’t 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:
sp_delete_backuphistory ‘2005-12-01’
This example would delete all backup information in the msdb tables that is older than December 1, 2005, 12:00 AM.
Conclusion
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.
