4

I am working with a large database on windows sql server 2008 R2 such that it has to run continuously 24x7 because of that it is not possible to restart the server time to time. It is kind of monitoring system for big machines. Because of this SQL server error logs are growing too big even some times up to 60-70 GB at a limited sized hard drive. I can't delete them time to time manually. Can someone please suggest a way using which I can stop creation of such error logs or recycle them after sometime. Most of the errors are of this kind --

Setting database option RECOVERY to simple for database db_name

P.S.- I have read limiting error logs to 6 etc. But that didn't help.

Mohit
  • 79
  • 2
  • 6

1 Answers1

2

For SQL instance prior to SQL 2012

Cycle the error logs with EXEC sp_cycle_errorlog, you may want to consider increasing the number of logs you keep if you cycle the error logs regularly.

You can set up a SQL Agent job with a T-SQL step. All it has to do is EXEC sp_cycle_errorlog. Schedule the SQL Agent job to run as frequently as you’d like and you’re good to go. The upside of this approach is that it’s automatic and the SQL Server error logs will be more granular, making it easier to find the error messages you’re looking for. Source, brentozar.com

For SQL 2012 and later you can set the max log file size. Again you may want to consider increasing the number of logs you keep if you limit the error log size.

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

Source, microsoft.com

James Jenkins
  • 1,954
  • 1
  • 24
  • 43