Shulou(Shulou.com)05/31 Report--

Clean up the SQL SERVER error log to solve the operating system error. In view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

The SQL SERVER 2008 R2 database built in the UAT environment has always been used normally, but recently it was found that nothing could be done in Sharepoint. At first, I thought there was something wrong with the configuration (because I had been studying some new applications and integrations and needed to be tested constantly), but later I found that the database hard disk ran out of space, so naturally I couldn't store any data. So at first, we started to clean up some useless database logs, and the disk space added a few gigabytes of capacity, but when the situation remained the same the next day, the database hard disk was still full, and the problem still existed. The background carefully checked the capacity of all the databases (because at first I thought that the database space was growing too fast every day to fill the hard disk), and found that there were only more than ten gigabytes, while the total hard disk space was 126g. So I further checked the disk space and found that there were only more than 30 gigabytes. At first, I wondered why there was a lack of nearly 90 gigabytes. Later, it was found that it was the relationship of the original windows account. The login account used before was limited and could not obtain all the space capacity of the disk, so after logging in with the administrator's account, I found that the capacity of an error log in SQL SERVER was nearly 90g. Finally, I found the reason why the disk was full. The next step is how to solve it. At first, after listening to the suggestion of my colleagues, I moved the SQL SERVER error log file directly to another hard disk by file clipping, and finally failed after several hours, indicating that the error log was occupied by the system process and could not be carried out in this violent way, so I went back to the right track and operated through the SQL SERVER maintenance command, and finally successfully cleared the 90g error log file. The specific process is as follows: because by default, SQL Server saves seven ErrorLog files named:

ErrorLogErrorLog.1ErrorLog.2ErrorLog.3ErrorLog.4ErrorLog.5ErrorLog.6

-- clear SQL Server error log file archive EXEC sp_cycle_errorlogGO

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 is complete. If DBCC outputs an error message, contact your system administrator.

Delete the 90g error log file manually.

It is also necessary to properly master some SQL SERVER maintenance commands in practice, and compared with ORACEL database, SQL SERVER maintenance is relatively simple.

This is the answer to the question about the solution to the operating system error caused by clearing the SQL SERVER error log. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.