Clear SQL logs

In SQL Server, whenever something is done, its log is stored in a file called ERRORLOG.n . The letter n at the end of the ERRORLOG  filename is a number that whenever SQL is restarted or executed, a new file with a new number is created. For example , ERRORLOG.1, ERRORLOG.2, ERRORLOG.3  , etc.

Over time, the number and size of these files increases and takes up a lot of space.

These files are located in the following path:

Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\

 It is recommended that you delete the log files regularly. To do this, you can run the following query in SQL.

EXEC sp_cycle_errorlog

To execute this command, you must pay attention to the selected database, because by default a query is executed on the master database. To run a query on a specific database, you can do the following.

USE DataBaseName EXEC sp_cycle_errorlog

By executing this command, all logs related to the desired database will be deleted.

If you want this to work as a job  , you can create the same command EXEC sp_cycle_errorlog as a timed job that runs at specified intervals and clears logs.

Another thing about logs is that SQL holds 6 log files by default. If you want to change the number of log files stored, you need to do the following:

• Open the Management folder in SQL Server Management.
• Right-click on SQL Server Log and select Configure.
• Select Limit the number of error log files before they are recycled.
• In this section you must specify how many log files you want to keep. This number should be between 6 and 99.

In SQL 2017, you can also specify the maximum size of log files in the Maximum size for error log file in KB section. If you change this value to 0, the file size will be unlimited.

Powered by Froala Editor