Identify and Correct a Large SQL Server Error Log

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


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

We know your day is hectic and you don't necessarily have time to research new topics and solutions every day, but we can keep you informed.

Take 30 seconds to register for our newsletter and look for free educational content to help you grow your career. >> REGISTER HERE <<

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
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.

sql server logs

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.

sql agent logs

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.

 

sql server error log settings

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

sql server error log settings

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.

sql server agent log

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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Jan Potgieter Jan Potgieter has years of experience in Information Technology which ranges from Unix Administration through System Administration and development, Web and Database Development and Administration and applying Agile methodologies.

View all my tips


Article Last Updated: 2022-07-19

Comments For This Article




Friday, July 22, 2022 - 4:05:19 PM - Tim Back To Top (90305)
I like to use the microsoft tiger teams adaptive cycle error log found on their github page:

https://github.com/Microsoft/tigertoolbox/blob/master/MaintenanceSolution/4_job_AdaptiveCycleErrorlog.sql

Thursday, July 21, 2022 - 4:03:01 AM - DirectDeals Back To Top (90292)
Nice post. Thanks for sharing here.

Tuesday, July 19, 2022 - 2:40:21 PM - Leon Back To Top (90288)
Hi Jan,

Itís a very handy procedure which I use for years! I put those two commands in a job which runs every month so I cycle both logs.


download














get free sql tips
agree to terms