To clear the SQL Server error log solution for operating system errors _mssql

Source: Internet
Author: User
Tags microsoft sql server mssql mssqlserver server error log

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 hard disk total space 126G, so further check this disk space, found only 30 g, initially feel very puzzled, Why is there a shortage of near 90G? Later found that the relationship between the original Windows account, previously used with limited access to the login 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 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 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.