Most of us get caught up in fragmentation, finding the slowest queries, and looking at new features. We forget the little things that make managing a SQL Server easier – like cylcing the SQL Server error logs.
The SQL Server error log is a file that is full of messages generated by SQL Server. By default this tells you when log backups occurred, other informational events, and even contains pieces and parts of stack dumps. In short, it’s a treasure trove of information. When SQL Server is in trouble, it’s nice to have this available as a source of information during troubleshooting.
Unfortunately, if the SQL Server error log gets huge, it can take a long time to read the error log – it’s just a file, after all, and the GUI has to read that file into memory.
It’s possible to cycle the SQL Server error log. Cycling the error log starts a new file, and there are only two times when this happens.
- When SQL Server is restarted.
- When you execute
When SQL Server cycles the error log, the current log file is closed and a new one is opened. By default, these files are in your SQL Server executables directory in the
MSSQL\LOG folder. Admittedly, you don’t really need to know where these are unless you want to see how much room they take up.
SQL Server keeps up to 6 error log files around by default. You can easily change this. Open up your copy of SSMS and:
- Expand the “Management” folder.
- Right click on “SQL Server Logs”
- Select “Configure”
- Check the box “Limit the number of error log files before they are recycled”
- Pick some value to put in the “Maximum number of error log failes” box
- Click “OK”
It’s just that easy! Admittedly, you have to do this on every SQL Server that you have, so you might just want to click the “Script” button so you can push the script to multiple SQL Servers.
You can set up SQL Server to automatically rotate your error logs. This is the easiest part of this blog post, apart from closing the window.
To cycle error logs on a regular basis, restart your SQL Server nightly.
You can set up a SQL Agent job with a T-SQL step. All it has to do is
EXEC sp_cycle_errorlog. Schedule the SQL Agent job to run as frequently as you’d like and you’re good to go. The upside of this approach is that it’s automatic and the SQL Server error logs will be more granular, making it easier to find the error messages you’re looking for.
It’s Just That Easy!
Cycling the SQL Server error log is easy – you just need a regularly scheduled agent job. Rotating the logs makes it easier to find error messages. Let’s face it – you’re only looking for error messages when there’s a problem. That’s all there is to rotating the error logs.