Database Log clearing text tutorial

Source: Internet
Author: User
How can I clear database logs without stopping? Here, taking a database SQLSERVER2005 as an example, the log file reaches more than 100 GB and cannot be cleaned up all the time. I think of many ways: for example, the following SqlServer log clearing expert, it can be used for database logs of various versions of SQLServer7, SQLServer2000, and SQLServer2005.

How can I clear database logs without stopping? Here, we take a database SQL SERVER 2005 as an example. When the log file reaches more than 100 GB, it cannot be cleaned up all the time. We thought of many ways: for example, the following SqlServer log clearing expert, it can be used for database logs of SQL Server 7, SQL Server 2000, and SQL Server 2005 versions.

How can I clear database logs without stopping? Here, we take a database SQL SERVER 2005 as an example. When the log file reaches more than 100 GB, it cannot be cleaned up and many methods have been taken:

For example, the following SqlServer log clearing expert can be used to clear database logs of SQL Server 7, SQL Server 2000, and SQL Server 2005 versions.

This tool can be cleaned once it is cleared. So that you no longer have to worry about the database log files exceeding several hundred megabytes or GB level.

The second method is to use a relatively dead method. Use separation, delete the log file, and then append it to generate a new log file.

Remember to back up the SQL before performing the operation.

The first step is to open the Enterprise Manager.

Before separation, it is best to shut down the programs that are connecting to the database such as IIS. Otherwise, they will not be able to be separated for a long time.

You can also choose to delete the link so that the separation may be faster.

Locate the hard disk where the database is located.

We can back up qq2.mdf for the sake of security, and then re-name the file qq2_log.ldf (there is no place to back up files of GB ).

Then we can append the database.

Append to the mdf file. Note: The following message indicates that the. ldf file cannot be found.

Ignore him. Select. ldf and delete it.

Then confirm that the restoration is successful. The system automatically generates a new ldf file 504KB

We recommend that you first use the tool above to clear the log. If it cannot be clear, you can use this method to delete the log.

Finally, I would like to remind you to pay attention to backup again !!

SQL2008 contraction log
Because SQL2008 optimizes file and log management, the following statements can be run in SQL2005 but have been canceled in SQL2008:
(SQL2005)

Go

Go
USE DNName

Go
--------------------------------------------------------------
(SQL2008 ):
Log cleanup in SQL2008 must be performed in simple mode. After the cleanup is completed, the log is called back to full mode.

GO

GO

GO

GO

GO

GO



GO



GO

Advantage: it takes a short time to clear logs. A 90 GB log can be cleared in about minutes. After the log is cleared, make a full backup within minutes.
.
Disadvantage: however, it is best not to use this action frequently because its operation will cause system fragmentation. In normal state, logs and DIFF backups can be truncated.
The appropriate environment used by this statement: when the system LOG file is abnormally increased or the backup LOG time is too long, it may affect the production.

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.