MSSQL clears logs and deletes log files

Source: Internet
Author: User
Recent projects mainly archive data and pull the data from one database to another, resulting in a large log file for the new database; or the log files are continuously increased during the use of the database, this reduces database performance and occupies a large amount of disk space. So I want to delete the log file. The simplest is to detach the database first-> delete logs

Recent projects mainly archive data and pull the data from one database to another, resulting in a large log file for the new database; or the log files are continuously increased during the use of the database, this reduces database performance and occupies a large amount of disk space. So I want to delete the log file. The simplest is to detach the database first-> delete logs

Recent projects mainly archive data and pull the data from one database to another, resulting in a large log file for the new database; or the log files are continuously increased during the use of the database, this reduces database performance and occupies a large amount of disk space. So I want to delete the log file. The simplest is to separate the database first, delete the log file, and then append the data. I need to call it in SSIS, so I already use SQL scripts.

There are two simple methods to clear logs:

An additional separation method:

1. First, separate the database. before separating the database, make sure to back up the database in full. Select database -- Right-click -- task -- detach. Select Delete Connection in Chinese medicine!

After the database is detached, the database that has been detached is invisible to the Database List.

2. Delete the LOG files of the database. ldf files of the corresponding database under the database folder

3. When attaching a database, the system will remind you that the log file cannot be found. A new log file is automatically created after it is appended to clean up the database.

Ii. Clear mssql logs using SQL statements

Dump transaction TestDB WITH NO_LOG clear logs

Dbcc shrinkfile ('testdb _ log', 1) shrink database files

Backup log TestDB WITH NO_LOG truncation transaction LOG

This command is not supported in SQL Server 2008 and can be used in SQL Server 2005 and 2000.

First, we need to obtain the path of the database file:

Declare @ logfilename varchar (100)

Declare @ datafilename varchar (100)

Select @ logfilename = physical_name from sys. database_files where type = 1

Select @ datafilename = physical_name from sys. database_files where type = 0

Switch to the master to detach the database.

Use master

Exec sp_detach_db @ dbname = 'testvfa ';

The next step is to delete the database log file.

---- Remove file

DECLARE @ Result int

DECLARE @ FSO_Token int

EXEC @ Result = sp_OACreate 'scripting. FileSystemObject ', @ FSO_Token OUTPUT

EXEC @ Result = sp_OAMethod @ FSO_Token, 'deletefile', NULL, @ logfilename

EXEC @ Result = sp_OADestroy @ FSO_Token

The last step is to attach the database.

Exec sp_attach_single_file_db @ dbname = 'testvfa ', @ physname = @ datafilename

Note: Ole Automation Procedures is disabled by default. We need to enable it.

Exec sp_configure 'show advanced options', 1;

RECONFIGURE;

Exec sp_configure 'ole Automation Procedures ', 1;

RECONFIGURE;

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.