solution that clears the SQL Server error log for operating system errors

The SQL SERVER 2008 R2 database that was previously built in the UAT environment has been used fairly well, but recently found that nothing can be done in SharePoint, starting to think that there is something wrong with the configuration (because of the ongoing research on some new applications and integrations that need to be constantly tested), But later found that the database hard disk is not a bit of space, so naturally can not save any data, so the first start to clean up some useless database log, disk space a few G capacity, but wait until the next day is still the case, the database hard disk is full, the problem still exists, The background carefully checked the capacity of all the databases (because the original thought is that the database space grew too fast every day to fill the hard disk), found only more than 10 g, and the total space of the hard disk 126G, so further check this disk space, found only 30 g, at first feel very puzzled, why will be missing will be nearly 90G ? later found that the relationship between the original Windows account, previously used with limited access to the account, unable to obtain all the space capacity of the disk, so changed the administrator's account login, found that the SQL Server has a bad log capacity will be nearly 90G, finally found the disk full reason, The next step is how to solve it.

At first, I listened to my colleagues ' suggestions directly moving this SQL SERVER error log file directly to another hard drive through file clipping, it took hours to end up with a failure, indicating that the error log was taken up by the system and could not be done in this violent way, so get back on track, through the SQL The Server Maintenance command operates, and finally successfully clears the 90G error log file, as follows:

By default,SQL Server saves 7 ErrorLog files , named:

ErrorLog

Errorlog.1

Errorlog.2

Errorlog.3

Errorlog.4

Errorlog.5

Errorlog.6

--Clear SQL Server error log file archive

EXEC Sp_cycle_errorlog

Go

Performing an exec sp_cycle_errorlog creates a new errorlog and then deletes the errorlog.6. is the first out (queue similar situation) so that the cycle of 6 times you can refresh the errorlog.

When the following error message appears in the query window:

Message 17049, Level 16, State 1, Process Sp_cycle_errorlog, line 9th

An operating system error ' 5 (access denied) has occurred. ) ', the error log file cannot be cycled from ' C:Program filesmicrosoft SQL servermssql10_50.mssqlservermssqllogerrorlog.5 ' to ' C:Program FilesMicrosoft SQL servermssql10_50.mssqlservermssqllogerrorlog.6 '. Processes outside of SQL Server may prevent SQL Server from reading these files. As a result, error log entries may be lost and may not be possible to view some SQL Server error logs. Make sure that no other process has locked the file into write-only access. "

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

Manually delete the 90G error log file.

With this experience, proper mastery of some SQL Server maintenance commands is also necessary in practice, and SQL Server maintenance is relatively simple relative to the Oracel database.