Monday, December 21, 2009

SQL Server 2005 Error log manaagement

By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

To cycle or force a new error log to be created you can restart your SQL server or truncate the error log using the Database Consistency Check (DBCC) command DBCC ErrorLog as in the example here:

Exec ('DBCC ErrorLog')

Exec Sp_Cycle_Errorlog


By default, the SQL Server error logs are kept in the SQL Server's Log subfolder. By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6


Limit the number of the error log files before they are recycled
Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of SQL Server is started. SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.
Maximum number of error log files
Specify the maximum number of error log files created before they are recycled. The default is 6, which is the number of previous backup logs SQL Server retains before recycling them.To open the Configure SQL Server Error Logs dialog box
In Object Explorer, expand your server, expand Management, right-click SQL Server Logs, and then click Configure.

1 comment:

  1. I have heard about another way of access 95 repair tools. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues

    ReplyDelete