INF: How to Archive More Than Six SQL Server Errorlogs

ID: Q230590

The information in this article applies to:

Microsoft SQL Server versions 6.5, 7.0

SUMMARY

This article has an example of a start up, or autoexec, stored procedure called sp_archivelog6 that will archive an infinite number of SQL Server errorlogs.

MORE INFORMATION

Database Administrators may wish to keep more than the default six archived (plus one current) SQL Server errorlogs. In SQL Server 6.5, the number of archive-able errorlogs is limited to six. SQL Server 7.0 has been enhanced to provide a means of archiving a finite but customizable number of errorlogs.

Using the procedure in this article does have a trade off as the archives will consume more drive space.

Edit the attached script if your path to your errorlogs is not c:\mssql\log (you also may need to alter variable sizes). You can test the stored procedure by restarting the service a few times and 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 following procedures' copy command can also be logged in the active errorlog if the no_output argument is removed.

Sample Code


USE MASTER
GO

IF EXISTS
(
SELECT * FROM sysobjects
WHERE id = OBJECT_ID('dbo.sp_archivelog6')
AND sysstat &amp; 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

REFERENCES

For more details on increasing the number of SQL Server Error Logs, please see the following article in the Microsoft Knowledge Base:

Q196909 INF: How to Increase the Number of SQL Server Error Logs

Additional query words:


Keywords          : SSrvAdmin SSrvErr_Log kbSQLServ650 kbSQLServ700 
Version           : winnt:6.5,7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: June 10, 1999