ERRORLOG still growing up

Question

Hi,

I have SQL Server 2008 R2 SP1 installed. I have installed SharePoint 2010 with a database. My SQL databases are situated at drive E:\.

My problem is that the ERRORLOG file situated in: "E:\SQL Server\MSSQL10_50.SHAREPOINT\MSSQL\Log" is still growing up (about 2MB/min.) This file is not possible delete when Sharepoint is running-when I want to delete it I must stop SharePoint DB. How can I change it? I do not need this log file with this huge capacity.

---

Petr Weiner

Answer 1

http://blog.sqlauthority.com/2010/11/09/sql-server-recycle-error-log-create-new-log-file-without-server-restart/

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

once you have cycled the log using the command in the link Uri posted, you should examine the new log to find out why it's growing so quickly.  If whatever's generating the excessive logging is left unattended, you'll find yourself in the same position very quickly

---

Thanks, Andrew

Answer 3

Hi,

To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log.  Depending on the growth rate of the SQL Server error log dictates when sp_cycle_errorlog should be issued.  Reference the code below as an example.

EXEC master.sys.sp_cycle_errorlog; -- Expected successful output -- DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Next, the easiest means to address this need would be to schedule a SQL Server Job to support the need.  Reference the SQLServer2005_CycletheErrorLog_Job.txt as a point of reference.

Finally, to address not loosing the historical SQL Server error log, the number of logs should be expanded beyond the default of 7.  The maximum number of logs is 99 for the SQL Server error log.  When it comes to expanding the number of SQL Server error logs, follow these steps:

• Open Management Studio
• Navigate to root | Management folder | SQL Server Logs folder
• Right click on the SQL Server Logs folder and select the 'Configure' option
• Select the 'Limit the number of error log files before they are recycled' check box
  • Reference the screen shot below as a point of reference
• Enter the desired number of error logs in the 'Maximum number of error log files'
  • Reference the screen shot below as a point of reference   
• Press the 'OK' button to save the configuration

Alternatively, the following code can be used to automate the process across multiple SQL Servers:

USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 50 GO

SQL Server Agent Error Log

The SQL Server Agent error log follows much of the same paradigm as the SQL Server error log.  The recycling process is completed by the sp_cycle_agent_errorlog in the MSDB database.

EXEC msdb.dbo.sp_cycle_agent_errorlog; -- Expected successful output -- Command(s) completed successfully.

In terms of configuring a specified number of SQL Server Agent error logs, this is not possible.  Only the current and 9 additional (a total of 10) SQL Server Agent error logs will be retained.  What can be configured are the type of messages (Errors, Warnings, Informational ) that are written to the SQL Server Agent error logs.  To access this interface, follow these steps:

• Open Management Studio

• Navigate to root | SQL Server Agent | Error Logs folder

• Right click on the Error Logs folder and select the 'Configure' option, reference the screen shot below

To automate this process across servers, reference the sp_set_sqlagent_properties system stored procedure.  Below outlines a sample execution.

USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7 GO

---

Ahsan Kabir