Large SQL Server Error Log using Excessive Disk Space

By: Jan Potgieter | Updated: 2022-07-19 | Comments (3) | Related: > Error Logs

Problem

A customer sent an email complaining the drive on a Windows server was full and a lot of space was taken up by a folder that SQL Server uses. I did a little investigation and found what the issue was and how to resolve as shown below.

Solution

I remember that this has happened before and thought this would be a quick fix as the server ran out of space before due to a database transaction log. The database log files were stored on the D: drive, but the issue was on the C: drive. Doing some more investigating, I discovered the SQL Server Log files were creating the problem.

After browsing the C:\ drive, I found the location of the file that was taking up a lot of space, which is below:

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log

The filename in question was named ERRORLOG, just ERRORLOG with no extension. The file was 40GB in size. I tried to delete the file, but I could not because the file was in use, so that is when I started doing some research on how to clean up the ERRORLOG file.

SQL Server Error Log Files

When you open up SSMS and log onto a SQL Server, expand Management > SQL Server Logs this is where you can access the ERRORLOG files as shown below. Even though they display as Current, Archive #1, etc. the files behind the scenes are named ERRORLOG, ERROROG.1, etc. If you right-click on one of these you get an option to View SQL Server Log which lets you see the contents.

The Current error log file is the one in use and is open by SQL Server, so that is why when we tried to delete the ERRORLOG file it was not possible since the file was in use.

If you go to the SQL Server Agent section in SSMS and go to Error Logs, you can see the SQL Agent log files. These also show as Current, Archive #1, etc., the but actual file names are SQLAGENT.OUT, SQLAGENT.1, etc. You can also right-click on one of the entries and get an option to View Agent Log and see the contents.

Cycling SQL Server Error Logs

Since my delete attempt was not successful, I tried to see what we could do in SSMS to fix the problem. If you right-click on SQL Server Logs, you get a menu option for Configure.

I clicked on Configure and got this screen, but didn't see a way to clean up the big file.

After doing more research, I found this command that will recycle the SQL Server Log files.

EXEC sp_cycle_errorlog; 
GO

The above SQL command closes the current error log file and cycles the error log extension numbers just like a server restart or a restart of SQL Server will do and creates a new log file called ERRORLOG. Using sp_cycle_errorlog enables you to cycle the error log files without stopping and starting the server.

Each time the error log is cycled, the current error log is renamed to errorlog.1, errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on.

Now that we cycled the error logs, we can go back to the directory and delete the large file which will now be named ERRORLOG.1.

Cycling SQL Server Agent Log Files

There is also an option to cycle the Agent Log files, but this can be done through SSMS or using a SQL command.

If you right-click on Error Logs under SQL Server Agent, you get this menu. You can see there is a menu option to Recycle.

You can also use this command to cycle the Agent Log files.

USE msdb; 
GO 

EXEC dbo.sp_cycle_agent_errorlog; 
GO

The above options close the current SQL Server Agent error log file and cycles the SQL Server Agent error log extension numbers just like a server restart.

Every time SQL Server Agent is started, the current SQL Server Agent error log is renamed to SQLAgent.1, SQLAgent.1 becomes SQLAgent.2, SQLAgent.2 becomes SQLAgent.3, and so on. Using these options enables you to cycle the Agent log files without stopping and starting the server.

Next Steps

Read these related articles:

About the author

Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-07-19

Identify and Correct a Large SQL Server Error Log