SQL Server: remove a large errorlog

Like any good program, the Microsoft SQL Server DBMS keeps a history of actions and errors encountered by the database server. These files named ERRORLOG can be very large depending on the errors archived. But to clean up or recover disk space, you may want to delete these large files. Unfortunately, it is not possible to delete all files, at least not the ERRORLOG file in use. SQL Server prevents deleting an ERRORLOG file that is currently in use by the DB server.

We can of course stop the MSSQLSERVER service or restart the computer, but this is not a solution in a production environment. This tutorial therefore explains how delete a very large ERRORLOG file without stopping SQL Server production.

Note: the errorlogs files are not there by chance, it is first necessary to check what fills this error log and to correct the databases, requests or applications which generate them.

Delete an ERRORLOG log file from SQL Server

1. Go to the Microsoft SQL Server installation folder, by default a path of the type C: Program Files Microsoft SQL Server

2. Open the SQL Server version number folder, for example MSSQL13.MSSQLSERVER for SQL 2016.

3. Then go to MSSQL, Log to see which files weigh several GB.

Microsoft SQL Server log errorlog cycle tutorial

4. If these are ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4… files which are very large, it will suffice to delete them (with or without prior saving).

In the event that ERRORLOG “quite simply” is large, it will only be possible to delete it from Explorer on one condition: if we stop or restart the “SQL Server (MSSQLSERVER)” service from Windows Services. Another solution, split the current ERRORLOG file so that the SQL server creates a new, empty one, so that we can recover the GB unnecessarily occupied by logs.

Cut an ERRORLOG file

1. Open an Administrator type connection (sa) with Microsoft SQL Server Management Studio software or other tool to run queries on the server (not just on a database).

2. Copy / paste / execute the following query to create a new log cycle, that is to say a rotation to write the log to a new error log file:

EXEC sp_cycle_errorlog;

Microsoft SQL Server log errorlog cycle tutorial

3. The returned result should read: “DBCC execution completed. If DBCC has sent you error messages, contact the system administrator. “

4. The current ERRORLOG file is passed to a numbering (ERRORLOG.1) which can now be safely deleted, without affecting the applications and connections maintained on the SQL server and its databases.

Microsoft SQL Server log errorlog cycle tutorial

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button

Adblock Detected

Please consider supporting us by disabling your ad blocker