Remove a Large Errorlog In SQL Server
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 archived errors. 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 you from deleting an ERRORLOG file that is currently used by the DB Server.
We can, of course, stop the MSSQLSERVER service or restart the computer but it is not a solution in a production environment. This tutorial explains how to erase a very large ERRORLOG file without stopping production of SQL Server.
Note: error logs files are not there by chance, it is first necessary to check what fills this history of errors and correct the bases, requests or applications that generate them.
Delete an ERRORLOG log file from SQL Server
1. Go to the Microsoft SQL Server installation folder, by default a path of type C: \ Program Files \ Microsoft SQL Server
- Open the folder number of the SQL Server version, for example, MSSQL 13 .MSSQLSERVER for SQL 2016.
Then go to MSSQL, Log to see which files weigh more than one GB.
If they are ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4 … which are very big, it will be enough to delete them (with or without previous backup).
In the case where ERRORLOG “any short” is bulky, it will be possible to delete it from the Explorer only on one condition: if we stop or restart the “SQL Server (MSSQLSERVER)” service from the Windows Services. Another solution, split the current file ERRORLOG so that the SQL server creates a new one, empty so that we can recover the Go unnecessarily occupied by logs.
Cut an ERRORLOG file
1. Open an Administrator type connection (sa) with the Microsoft SQL Server Management Studio software or another tool to run queries on the server (not just on a base).
- Copy/paste / execute the following query to create a new log cycle, that is, a rotation to write the log to a new error log file:
The returned result must be this: “Execution of DBCC complete. If DBCC has sent you error messages, contact your system administrator. “
The current ERRORLOG file has been changed to a numbering (ERRORLOG.1) that can now safely be removed without affecting the applications and connections maintained on the SQL server and its databases.