SQL Server error logs gives use information about processes that are successfully completed; help use detect current or potential problem areas; kernel messages or other server-level error messages.
Unless we create new error log periodically it will become hard to manage and we have to cycle error logs. Restart SQL Server service to create a new log each time and SQL server retains backups of the previous six logs and we could maintain the maximum of 99 error logs at the same time. We could use a system stored procedure to can be used for cycling SQL Server error logs files without having to restart the instance of SQL Server.
Let us see how we could use system stored procedures to cycle error logs in action
To cycle the error log without restarting we could run the following system stored proc
Exec master.dbo.sp_cycle_errorlog |
To change the number of error logs use the following script and change the default number 6 with any number less than 99.
USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 6 GO |
If you would like to make change to the Error logs using SSMS, start by right clicking on SQL Server Logs under Management folder then select Configure
Click on Limit the number of error log files before they are recycled check box and in the Maximum number of error log files text box enter any number of log files between 6-99 that is convenient for your to maintain.
You may also want to schedule a Job for the error cycling so that you done have to remember running the sys proc by yourself everyday and you could do that by creating new job.
Let us create a new job for our error cycling procedure by right clicking one the jobs folder under SQL Server Agent and select New Job…
then enter name and owner of the Job
Next click on Steps tab on the left and click on New… at the bottom. A new dialog named Job Step Properties will be opened. Enter the Step name and the command which is the system stored procedure the click on OK
Now click on Schedule on the left and click on New… at the bottom. A new dialog named Job Schedule Properties will open. Enter any appropriate name for the schedule and select the occurrence ad time for your schedule to run. Finally click on OK and you will now an error log cycle that will run on the selected schedule.
Read More
http://technet.microsoft.com/en-us/library/ms182512.aspx
http://technet.microsoft.com/en-us/library/ms187885(v=sql.105).aspx
