MSSQL emptying log Delete log file

The most recent projects are the archiving of data, pulling data from one database to another, resulting in a very large log file for the new database, or the fact that the database is being used to increase the log files, which can degrade the performance of the database and consume a lot of disk space. So I want to delete the log file. The simplest is to detach the database first-delete the log file-the last additional data that I need here is called in SSIS, so the SQL script is primarily.

Two simple ways to clear the log:

One separation additional method:

1, the first separation of the database, separate the database must do a full backup of the database, select the database-right key-task-separation, which drug tick delete connection!

The detached database will not be visible in the database list after separation.

2, delete the database log file database folder of the corresponding database LDF files

3, attach the database, the additional time will be reminded that the log file can not be found. When attached, a new log file is automatically created to clean up the database.

Second, the SQL statement to clear MSSQL log

DUMP TRANSACTION testdb with no_log purge log

DBCC shrinkfile (' Testdb_log ', 1) Shrink database file

BACKUP log TestDB with no_log truncate transaction logs

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

First we need to get the path to 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

Then switch to master and 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 is to append the database

exec sp_attach_single_file_db @dbname = ' TESTVFA ', @physname = @datafilename

Note: The default OLE Automation procedures is disabled we need to enable it

exec sp_configure ' show advanced options ', 1;

Reconfigure;

exec sp_configure ' Ole automation procedures ', 1;

Reconfigure;