Two Methods for clearing SQLServer logs

Source: Internet
Author: User

When the log file is full and the SQL database cannot write the file, you can use either of the following methods:
One method: Clear logs.
1. Open the query analyzer and enter the command
Dump transaction database name WITH NO_LOG
2. open the Enterprise Manager and right-click the database you want to compress -- all tasks -- shrink database -- shrink file -- Select log file -- select to shrink to XXM In the shrink mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

Another method is risky because the log files of SQL SERVER are not immediately written to the master database file. improper processing may cause data loss.
1: Delete LOG
Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database
2: delete LOG files
Attach Database Enterprise Manager-> server-> database-> right-click-> attach Database
This method generates a new LOG with a size of more than 500 K.

Note: The first method is recommended.

If you do not want it to become larger later.
Use in SQL2000:
Right-click the database and choose Properties> Options> fault recovery> model> select simple model.
Or use an SQL statement:
Alter database name set recovery simple

In addition, Truncate log on checkpoint (this option is used for SQL7.0. in SQL 2000, the fault recovery model is selected as a simple model) when the CHECKPOINT command is executed, if the transaction log file exceeds 70% of its size, the content will be cleared. In the development database, this option is often set to True Auto shrink to regularly check the database. When the database file or when the unused space of the log file exceeds 25% of its size, the system will automatically reduce the file so that the unused space is equal to 25%. When the file size does not exceed the initial size when it is created, the file size after the file is not reduced must be greater than or equal to its initial size for the transaction the log file can be reduced only when it is backed up or the Truncate log on checkpoint option is set to True.

Note: Generally, the default attribute of the database established is set, but the database attribute is changed in case of exceptions. please clear the log and check the preceding attributes of the database to prevent transaction logs from being full again.

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.