SQL Tips - Finding space in a pinch with sp_cycle_errorlog
I can’t tell you off the top of my head how many times I’ve received customer calls reporting Online5 errors which end up being caused by lack of space on the SQL Server's C drive. Luckily, we document incidents here at Escape Technology and it looks like I’ve had 6 occurrences in the past 2 years regarding errors due to drive space. Often times there is not even enough drive space to open SQL Server Management Studio or view a Log file. When I do need to free up space on a SQL Server, I check the usual suspects first:
- Old backup files that can be deleted and/or moved to another location
- Old stack dumps, crash dumps, mini dumps in the \LOG directory that can be deleted
- Large files saved to User Desktops or Document folders that can be removed
- The Recycle Bin can also provide a goldmine of large files hogging up disk space; empty it to free up some space.
If you have exhausted your search for files to delete and are still low on drive space, the next step you might consider would be to reboot the SQL Server and perform some emergency maintenance while you have a couple spare megabytes available before SQL eats them up again. A SQL Server reboot will free up some drive space, but it will also create a group of angry users who cannot access the system for several minutes while services come back up. Before you reboot your server, take a look at cycling the SQL Error logs.
It is not uncommon to find SQL Error Logs that are over 2GB in size, and when you have 5 or more logs, they can fill a drive very quickly. I have found the sp_cycle_errorlog command very helpful to free up some space so that I can perform other emergency maintenance. Executing the sp_cycle_errorlog command closes the current error log file and cycles the error log extension numbers just like a server restart. Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on. sp_cycle_errorlog enables you to cycle the error log files without stopping and starting the server.
Now that you have some breathing room, try freeing up some space on the C drive when you are able to stop SQL Services.
- Move data\log files to another drive. NOTE: It is best practice to put Data and Log files on separate physical drives to optimize the I/O performance.
- Place SQL Error Logs on another drive.
The following Microsoft article also provides Storage Top 10 Best Practices.
If you have any handy hints, please let us know! We’d love to hear from you.