A long back I had written a blog on – SQL SERVER – Recycle Error Log – Create New Log file without Server Restart. Recently one of my blog reader emailed me with this question:
In our SQL Server, we are logging successful logins to ERRORLOG and due to this the file size keeps on increasing. I have read your blog about recycling error log. I have put a job in SQL Agent to recycle the logs every midnight. No problem so far. In near future, we will have audit in our company and as per their requirement, they want to have Errorlog worth 30 days (to check login success/failures). Since I have put midnight job and we have six archive errorlogs (ERRORLOG.1 to ERRORLOG.6) and that would cover last 6 days only.
To solve the problem, I can put a copy step to move the file somewhere else before midnight recycle. But again I also need to put logic to delete 30 days old file. Is there any way to achieve this in SQL Server without much efforts?
I thought I had blog covering this answer but surprisingly, I couldn’t find anything on my blog. So, here are various ways to achieve the same.
Once we connect to SQL instance via SSMS, we can go to “Management” Node and right click on “SQL Server Logs” and choose “Configure” as shown below.
Once we click on configure, the checkbox shown below would be unchecked by default and value would be shown as 6. That’s the reason we have files till ERRORLOG.6.
We can check the box and put the desired value in the box. Based on daily recycle of errorlog which my blog reader had and 30 days requirement, the value can be set to 30 to keep his auditors happy.
If you are not a fan of UI then below is the T-SQL which can be used to achieve the same change.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
Above is essentially changing the registry key called NumErrorLogs for this instance. Notice that SQL is invoking xp_instance_regwrite which doesn’t have instance related details as it detects the exact key internally. For my named instance “SQL2014” for SQL Server the exact key would be
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer as shown below
Hope this would help you in real world to keep more errorlogs than default if that is a requirement from your DBA/Network or auditor teams.
Reference: Pinal Dave (http://blog.sqlauthority.com)