The method of clearing error log by SQL server

Found that the database of SQL SERVER 2012 is inaccessible and keeps reporting errors. Check the reason, found that the database hard disk does not have any space, SQL SERVER has an error log capacity of nearly 128G. During the operation of the database, the SQL SERVER error log file cannot be directly moved to another hard disk by file clipping, because the error log is occupied by the system process and cannot be carried out through this violent way. You must use the SQL SERVER maintenance command to clear the 128g error log file as follows:

Because by default, SQL Server saves seven ErrorLog files named:

ErrorLog

ErrorLog.1

ErrorLog.2

ErrorLog.3

ErrorLog.4

ErrorLog.5

ErrorLog.6

-- clear the SQL Server error log file archive

EXEC sp_cycle_errorlog

GO

Executing EXEC sp_cycle_errorlog once produces a new errorlog, and then deletes the errorlog.6. First-in, first-out (queue-like situation) so that the errorlog can be refreshed six times.

When the following error message appears in the query window:

Message 17049, level 16, status 1, procedure sp_cycle_errorlog, line 9

Due to operating system error'5 (access denied.)' Cannot cycle the error log file from'C:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ Log\ ERRORLOG.5' to'C:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ Log\ ERRORLOG.6'. Processes outside SQL Server may prevent SQL Server from reading these files. As a result, error log entries may have been lost, and it may not be possible to view some SQL Server error logs. Make sure that no other process locks the file for write-only access. "

DBCC execution completed. If DBCC outputs an error message, contact your system administrator.

Delete the 90g error log file manually.

Through this experience, it is necessary to master some SQL SERVER maintenance commands in practice, and compared with ORACEL database, SQL SERVER maintenance is relatively simple.