Error Log File

Introduction

SQL Server Error Log is the best place for a Database Administrators to look for informational messages, warnings, critical events, database recover information, auditing information, user generated messages etc. SQL Server creates a new error log file everytime SQL Server Database Engine is restarted. This article explains how to recycle SQL Server Error Log file without restarting SQL Server Service.

Database administrator can recycle SQL Server Error Log file without restarting SQL Server Service by running DBCC ERRORLOG command or by running SP_CYCLE_ERRORLOG system stored procedure.

Note:- Starting SQL Server 2008 R2 you can also limit the size of SQL Server Error Log file. For more information see Limit SQL Server Error Log File Size in SQL Server. However, to increase the number of error log file see the following article for more information How to Increase Number of SQL Server Error Log Files.

Recycle SQL Server ErrorLog File using DBCC ERRORLOG Command

Execute the below TSQL code in SQL Server 2012 and later versions to set the maximum file size of individual error log files to 10 MB. SQL Server will create a new file once the size of the current log file reaches 10 MB. This helps in reducing the file from growing enormously large.

USE [master];
GO

DBCC ERRORLOG
GO

Recycle SQL Server Error Log File using SP_CYCLE_ERRORLOG System Stored Procedure

Use [master];
GO

SP_CYCLE_ERRORLOG
GO

Best Practice: It is highly recommended to create an SQL Server Agent Job to recycle SQL Server Error Log once a day or at least once a week.

Conclusion

This article explains how to Recycle SQL Server Error Log file without restarting SQL Server Service.

Read more: http://www.mytechmantra.com/LearnSQLServer/SQL-Server-Recycle-Error-Log-Without-Restarting-Service-DBCC-ErrorLog-or-SP_CYCLE_ERRORLOG/#ixzz3h4gmJhC3
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook

Limit SQL Server Error Log File Size in SQL Server 2012

Sept 12, 2014

Introduction

SQL Server Error Log contains valuable information which can be used by database administrators to troubleshoot issues with SQL Server. A typical Error Log file contain informational messages, warnings, critical events, database recover information, auditing information, user generated messages etc.

SQL Server Error Log file is initialized every time SQL Server Instance is started. Hence, if SQL Server is not restarted in a while then the error log file will grow very large and it can been extremely difficult to read the information stored within it during critical times. See the follow article to learn How to Recycle SQL Server Error Log file without restarting SQL Server Service.

This article explains how to limit the size of SQL Server Error Log file in SQL Server 2012 and later versions. As a best practice we highly recommend you to backup the registry settings before modifying it. See the follow article to learn How to Increase Number of SQL Server Error Log files in SQL Server.

Note: One can also limit the size of SQL Server Error Log File on SQL Server 2008 R2 apart from SQL Server 2012 and later versions.

Limiting the size of SQL Server Error Log File in SQL Server 2012 and later versions

Execute the below TSQL code in SQL Server 2012 and later versions to set the maximum file size of individual error log files to 10 MB. SQL Server will create a new file once the size of the current log file reaches 10 MB. This helps in reducing the file from growing enormously large.

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'ErrorLogSizeInKb'
    ,REG_DWORD
    ,10240;
GO

The below screenshot refers to the registry key prior to the limiting the size of SQL Server Error Log file in Windows Server 2008 R2 for SQL Server 2012.

 

In the below screenshot you could see a new entry added with the name ErrorLogSizeInKb along with the value as 10240 KB.

 

Since SQL Server Error Log hold critical information it is highly recommended to limit the size to a unit which prevents it from growing too large.

Conclusion

This article explains how to limit the size of SQL Server Error Log files in SQL Server 2012 and later versions

Read more: http://www.mytechmantra.com/LearnSQLServer/Limit-SQL-Server-Error-Log-File-Size-in-SQL-Server-2012/#ixzz3h4gx8lKb
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook

Introduction

SQL Server Error Log is the best place for a Database Administrators to look for informational messages, warnings, critical events, database recover information, auditing information, user generated messages etc. By default, there are six archive error log files along with the current file named ERRORLOG. This article explains how to increase the number of SQL Server Error Logs from the default value of six.

Whenever SQL Server is restated, a new ERRORLOG file is created and the previous file is renamed as ERRORLOG.1, and the second most recent error log will be renamed as ERRORLOG.2 and the last error log will have a name similar to ERRORLOG.n. See the follow article to learn How to Recycle SQL Server Error Log file without restarting SQL Server Service.

If you are looking for option to limit the size of ErrorLog file then see the following article for more information How to Limit SQL Server Error Log File Size in SQL Server 2008 R2 and Later Versions.

Best Practice:: It is highly recommended to increase the number of SQL Server Error Log files from the default value of six to a minimum of 30.

How to Increase Number of SQL Server Error Log Files Using SSMS

Follow the below mentioned steps to increase the number of SQL Server Error Log files in SQL Server 2005 and later versions.

1. Open SQL Server Management Studio and then connect to SQL Server Instance

2. In Object Explorer, Expand Management Node and then right click SQL Server Logs and click Configure as shown in the snippet below.

 

3. In Configure SQL Server Error Logs window you can enter the value between 6 and 99 for the number of error logs and click OK to save the changes.

 

How to Increase Number of SQL Server Error Log Files Using TSQL Query

Execute the below mentioned TSQL query in SQL Server to increase the number of SQL Server Error Log file.

USE [master]
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'NumErrorLogs'
    ,REG_DWORD
    ,99
GO

In the below screenshot you could see a new entry added in registry with the name NumErrorLogs and has a value as 99.

 

Conclusion

This article explains how to increase the number of SQL Server Error Log files in SQL Server 2005 and later versions.

Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Increase-Number-of-SQL-Server-Error-Log-Files/#ixzz3h4gaE8vL
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook