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:
Hi Pinal,
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?
Regards,
<Name Hidden>
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.
Using SSMS
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.
Using T-SQL
If you are not a fan of UI then below is the T-SQL which can be used to achieve the same change.
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
GO
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 (https://blog.sqlauthority.com)
2 Comments. Leave new
Hello Pinal Sir,
I would like you ask you a small query.
The Full text search splits the search item which we send into words and then does the search. But i need it to search taking a part of word also. Can that be explicitly configured?
I would be grateful to you if you leave an answer to my query.
Thanks
Regards,
Suraj Sahoo
Increasing the number of error logs is a standard configuration for any servers I manage, but I am currently managing a server where I want to reduce an excessive number of logs. I’ve changed the Log files count to the preferred number and cycled the error log, but the physical log count stays at the larger value. What’s the recommended way to reduce the logs? I’d rather not have to manually delete the extra files.