MSSQL clears logs and deletes log files

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;