It’s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server.
One thing that hasn’t been possible before is setting the maximum size of individual SQL Server error logs before SQL Server triggers cycling to a new error log. Well it turns out that in SQL Server 2012 you can!
While in my post-con workshop at SQL Intersection in Las Vegas last week, Jan Kåre Lokna (a former Immersion Event attendee from Norway) discussed some code he’s been experimenting with and I just heard from him that he got it to work.
The following code will set the maximum size of each error log file to 5MB on a SQL Server 2012 instance:
USE [master]; GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 5120; GO
I’ve tested this on SQL Server 2008 R2 and it does not work there, so the registry key must be new for SQL Server 2012. This is really useful to protect against gigantic error log files caused by repeated crash dumps, for instance when messing around with DBCC WRITEPAGE :-)
[Edit: 10/23/15] Also, for SQL Server 2014 the registry keys have changed again:
USE [master]; GO -- Limit size of each file EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer', N'ErrorLogSizeInKb', REG_DWORD, 1024; GO -- Number of ErrorLog Files EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer', N'NumErrorLogs', REG_DWORD, 8; GO
Note: I’ve had anecdotal reports that on SQL Server 2014, the SQL Server 2012 method is the only one that works. Please experiment and see which works in your situation. I imagine the difference may be due to fresh-install vs. upgraded-in-place.
You can read a more in-depth description on Jan’s blog here.