515
Hi
How do I limit the size of Error log for SQL Server 2008 R2
C:\Program Files\Common Files\MIcrosoft Shared\Web server\Extensions\14\Data\MSSQL 10.sharepoint\mssql\log(error ;log)
I researched and found some command like "sp_errorlog"(but it will do some recycle of error log not sure what that means).
I went to SQL server Mgt studio --Configure.There u can give the number for error log but dont know how to limit the size of the error log
Can someone please help.
Hi NDingankar22,
For pre SQL server 2012 you can't set max log file size, but you can reinitialize error logs with sp_cycle_errorlog stored procedure, set an sql agent job to execute this stored procedure, and set it to run in the frequency you want, so this applies to your case (SQL 2008 R2)
sp_cycle_errorlog Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created.
For SQL server 2012 and later, you can set the max log file size:
USE [master]; GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 5120; GOCode
In both cases increasing the number of logs you keep will help you limit the error log size.
Source : support.microsoft.com/.../how-to-manage-the-sql-server-error-log
Best regards
Restarting the SQLserver will create a new Error log. But the old one is in GB and eating space. Can someone tell me how can i limit the size of error log. The error log of Gb was unable to open at my end
#1
André Arnaud de Cal...
294,261
Super User 2025 Season 1
#2
Martin Dráb
233,011
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
