I came across a problem this week with one of our SQL Servers whereby one of the drives was very low on space. Whenever I come across a disk space problem, I use my trusty friend Treesize which is a free download and it enables me to quickly find where the space is being consumed.
Having run the tool, I quickly found the culprit. It was the SQL Server error log consuming gigabytes of disk space.
Where is the SQL Server Error Log?
Typically inside the “Log” directory of your SQL Server instance so for example on my laptop it is here.
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Log
Recycle the SQL Server Error Log using sp_cycle_errorlog
You can view the logs and their contents in Management Studio by accessing the “Management” node and by either double clicking or right clicking the logs
So in order to get my disk space back, I ran a stored procedure called sp_cycle_errorlog which will create you a new log and move the old log to a new file called ErrorLog.1 inside your error log directory. sp_cycle_errorlog is installed with SQL Server.
Each time you run this command, a new log is created and the existing logs are cycled until the max number of allowed error logs is reached. So you get ErrorLog.1, ErrorLog.2 etc in your log directory. When the max logs is reached, the oldest file is removed.
I decided that I did not want to keep the logs as this was a development server and I was aware of what messages were consuming the space. For a production server, you may want to harvest the logs onto another drive before running sp_cycle_errorlog to completely remove the log files.
You can adjust the number of log files to be retained, the minimum is 6 and the maxium is 99 and this can be configured by right clicking the SQL Server Logs node and choosing the configure option.
For more information on sp_cycle_errorlog, you can visit this link