In this post I would like to mention a few things about the error log. As a DBA I have to check the error logs of my SQL servers many times a day. Checking the error logs of the SQL Servers is a fundamental part of my DBA morning checklist and even my DBA end of day checklist. If I ask you how can you configure the SQL Server Error Log, Could you give me an answer? If your answer is yes, I have to congratulate you, but if your answer is no, this article may help you to change that and hopefully help you out with a few other things.
If you want to know how to configure the error log of your SQL server, there is an article about it in msdn. To find the configuration options for the error log go to Management, then with the right button of the mouse click on SQL Server Logs and then select Configure, see the screenshot below.
An article by Jeremy Kadlec explains in detail how to configure the error log. You can find the article here.
There is not a way to fix the size of the SQL error log, but what you can do is apply a tip from Jeremy Kadlec’s article on limiting the size of the error log, the tip is to execute the “sp_cycle_errorlog” stored procedure, this procedure starts a new error log and archives the old one. Again this is not a way to fix the size of the error log, but a way of avoiding the error log to grow too much (when you have a serious problem the error log will still grow, keep that in mind).
SQL Server has by default 7 error logs, the most important one is the without an extension (ERRORLOG), the other error logs can be deleted if needed. In the case you have huge error logs that are consuming a lot of disk space, you can first run the “sp_cycle_errorlog” stored procedure and then delete or move the files after finding out what was the problem. Please, do not delete the error logs before checking the information in them.
I hope this post will be useful to you. If you have any questions please do not hesitate to contact me.