Method of compressing and deleting SQL Server transaction log

When doing a project to get a only 280M database backup, but in SQL Server 2000 recovery when waiting for a long time did not recover complete, it is incredible, so suspect there is something fishy. To the data file directory next look, sure enough to claim a close to 10G database log files! Databases that have never done so much data before, and have not done a database with 3 of years of database logging.

This is not very good for hard disk space I have exceeded the tolerable limit, and another 80M database reply after the generated log file is exaggerated to reach the 5g+! This is intolerable and cannot be forgiven.

At the beginning of the time do not understand, directly to the database log deleted, and found that the database can not be used, so Baidu a bit, found that a lot of online mention of the log compression and deletion technology, is what I want to grasp the content. Carefully sorted out, there are roughly such a few ways to solve.

Method One:

First step:

backup log database_name with no_log
或者 backup log database_name with truncate_only

--No_log and truncate_only are synonymous here, and any sentence can be executed casually.

Step Two:

1. Shrink all data and log files for a particular database and execute:

dbcc shrinkdatabase (database_name,[,target_percent])

--database_name is the name of the database to shrink, and target_percent is the percentage of the remaining free space that is required in the database file after shrinking.

2. Shrink data or log files from one particular database at a time, executing

dbcc shrinkfile(file_id,[,target_size])

--FILE_ID is the identification (ID) number of the file to shrink, to obtain the file ID, use the FILE_ID function or search the current database for sysfiles;target_size is the desired file size in megabytes (represented by integers). If not specified, DBCC SHRINKFILE reduces the file size to the default file size. Two DBCC can be brought with parameter notruncate or truncateonly, meaning to view online help.