It’s been a great day for British cycling with Bradley Wiggins becoming the first Briton to ever win the Tour de France, Chris Froome finishing second and Mark Cavendish winning the final stage. I have a huge amount of admiration for their dedication to the sport, it takes massive amounts of physical and mental stamina to compete at that level, not to mention awesome technique to be able to deliver so much power to the wheel.
Although we as SQL Server Professionals don’t have to go through that on a daily basis we still need to be disciplined, we still need to work well as a team. Technique and awareness is just as important in our field as it is theirs.
If like many people you have a critical system running SQL Server which must have something crazy like 5 9’s availability during the course of a year, then you probably won’t have rebooted your server or restarted your instance in quite some time. Is this a problem? In terms of availability no, in terms of management – it can be. The problem is that by default SQL Server will only cycle it’s error logs upon the SQL Server service restarting, this can lead to extremely large log files which will cause problems with whichever client you use to open it. It’s not just the time taken to open the the file, it also becomes more difficult to find the exact issue due to the sheer amount of data in the file. Of course we all check our logs on a daily basis don’t we?
Microsoft do have a way of allowing you to manage this more effectively in the shape of the stored procedure sp_cycle_errorlog. As a best practice it is recommended that you create a scheduled job which will run this command on a regular basis, this allows you as a troubleshooter a much easier way of finding the problem areas in your environment. So what happens when a log is cycled? You can think of it as a First In Last Out kind of deal, by default SQL Server will have 6 error logs that you can view within the GUI, when a new log is started every other log shifts down a position and the last log is aged out. On my Laptop the path to these logs can be found out C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_R1\MSSQL\Log the details that are underlined in italics are environment specific be sure to amend this to your version and instance details.
It is possible to change the number of log files that are stored, to do this;
- Open up SSMS and connect to the instance you wish to configure
- Expand the instance
- Expand the Management folder
- Right click on SQL Server Error Logs and choose Configure
As you can see from the screenshot you can enter a value of between 6 and 99. Changing this value will allow you to store more log files which can aid you if you are having issues with the SQL Server service continually stopping. It is also a best practice to ensure that this directory is backed up on a regular basis. It’s important to back up your user data, however backing up log files are often overlooked.
In the same way that you can cycle the SQL Error logs it’s also possible to do the same for the SQL Server Agent error logs. To view the error logs expand the SQL Server Agent folder then the Error Logs folder, unlike the SQL Server error Logs the default number of agent error logs is 10. To cycle the agent error log programmatically run the stored sp_cycle_agent_errorlog, again it is recommended to wrap this up in a scheduled job to make it easier to view error details. This procedure was introduced in SQL Server 2005, so if you are still using SQL Server 2000 then you will need to restart the agent service in order to cycle the log.
The image in this blog was supplied by http://www.flickr.com/photos/petitbrun/7352452738/ under the creative commons licence.
