How to archive more than six SQL Server error logs

Article translations Article translations
Article ID: 230590 - View products that this article applies to.
This article was previously published under Q230590
Expand all | Collapse all

On This Page

SUMMARY

This article describes how to archive more than six SQL Server error logs.

This article includes a sample startup (or autoexec) stored procedure named sp_archivelog6 that can archive an infinite number of SQL Server error logs.

Database administrators may want to keep more than the default six archived (plus one current) SQL Server error logs. In SQL Server 6.5, the number of error logs that you can archive is limited to six. In SQL Server 7.0, you can archive a finite, but customizable, number of error logs.

Note In SQL Server 2000, you can configure a finite number of error logs that are created before they are recycled by using SQL Server Enterprise Manager. As a result, you no longer have to create and run this stored procedure if you want to archive a finite number of error logs. In contrast, you have to create and run this stored procedure if you want to archive an infinite number of error logs. For example, you can use this stored procedure to archive all the error logs.

The procedure in this article does have a trade off however, because the archives take up more disk space.

Edit the attached script if your path to your error logs is not C:\Mssql\Log (you also may have to change variable sizes). You can test the stored procedure by restarting the service several times and by opening the ASCII file named Errorlog.yyyymmdd (in the SQLServer \Log folder). You can also edit the script to make the destination somewhere other than the \Log folder. The success or failure of the copy command in the following procedures can also be logged in the active error log if you remove the no_output argument.

Sample Code

USE MASTER
GO

IF EXISTS
(
SELECT * FROM sysobjects
WHERE id = OBJECT_ID('dbo.sp_archivelog6')
AND sysstat & 0xf = 4
)
DROP PROCEDURE dbo.sp_archivelog6
GO

CREATE PROCEDURE sp_archivelog6 AS

DECLARE @PathNoExt char(22)
DECLARE @PathISO char(30)
DECLARE @CopyAppendCmd char(129)
DECLARE @CopyCmd char(103)

-- Edit path below to point to your log folder.
SELECT @PathNoExt =
'C:\MSSQL\LOG\errorlog.'

SELECT @PathISO =
@PathNoExt + CONVERT(char(8), GETDATE(), 112)

SELECT @CopyAppendCmd =
'IF EXIST ' + @PathISO + ' COPY '
+ @PathISO + '+' + @PathNoExt + '6 '
+ @PathISO

SELECT @CopyCmd =
'IF NOT EXIST ' + @PathISO + ' COPY '
+ @PathNoExt + '6 ' + @PathISO

EXEC('xp_cmdshell "' + @CopyAppendCmd + '", no_output')
EXEC('xp_cmdshell "' + @CopyCmd + '", no_output')
GO

sp_makestartup sp_archivelog6
GO
				
In SQL Server 7.0 and SQL Server 2000, sp_makestartup has been replaced by sp_procoption. If you are using SQL Server 7.0 or SQL Server 2000, replace this line
sp_makestartup sp_archivelog6
				
with the following line:
sp_procoption sp_archivelog6, startup, true
				

REFERENCES

For more information about how to increase the number of SQL Server Error Logs, click the following article number to view the article in the Microsoft Knowledge Base:
196909 How to increase the number of SQL Server error logs

Properties

Article ID: 230590 - Last Review: July 16, 2008 - Revision: 4.3
APPLIES TO
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
Keywords: 
kbhowtomaster KB230590

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com