Methods for parsing SQL Server transaction log compression and deletion

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

This is not enough for hard disk space I have exceeded the limit can be tolerated, and another 80M database reply generated after the log file is exaggerated to achieve 5g+!    It is intolerable and cannot be forgiven. At the beginning of the time do not understand, directly deleted the database log, found that the database can not be used, so Baidu a bit, found on the internet a lot of mention of the compression and deletion of the log technology, is what I want to master the content. Carefully collated, roughly there are several ways to solve. ------------------------

method One: this is the database and the log to shrink, more trouble, is not what I want.

The first step:

BACKUP LOG database_name with NO_LOG

or 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, executing:

DBCC SHRINKDATABASE (Database_name,[,target_percent])

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

2. shrink a data or log file in a specific database at a time to perform

DBCC SHRINKFILE (File_id,[,target_size])

FILE_ID is the identification (ID) number of the file to be shrunk, to obtain the file ID, use the FILE_ID function or search in the current database for the file size (in integers) that the sysfiles;target_size is to represent in megabytes. If not specified, DBCC SHRINKFILE reduces the file size to the default file size. Two DBCC can take the parameter notruncate or truncateonly, the specific meaning to see the online Help.

-----------------------------------

method Two: This is exactly what I want, delete the original log file. But always prompt me in the operation of the database is in use, if I stop the database manager has to go into the Enterprise Manager to find the database to shrink, there is no way to give up, try a third.

The first step:

Back up the whole database first for the rainy.

Step Two:

After the backup is finished, execute the following statement in Query Analyzer:

EXEC sp_detach_db Yourdbname,true

Dismount this DB's registration information in MSSQL

Step Three:

Delete the log file in the same directory as the physical file of the log, or move the log file out of the directory

Fourth Step:

In Query Analyzer, execute the following statement:

EXEC sp_attach_single_file_db Yourdbname, '

D:\mssql\data\yourDBName_data.mdf '

Register the DB as a single file, and if successful, MSSQL will automatically generate a 500K log file for this db.

---------------------------------------

method Three: Perfect solution!

1. go to Enterprise Manager and select the database, such as demo

2. Detach database for all Tasks

3. to the database file storage directory, will be demo_log. LDF file deleted, just in case, you can cuff it or rename it

4. Enterprise Manager, additional database, choose Demo, this time you will see the log file This is a fork, do not matter, continue, this time the database will prompt you whether the database does not have a log to create a new, OK.

5. Remember to reset the database after you re-attach it.

If later, do not want it to become larger:

use under SQL2000:

On the database, right-click Properties--Options--model-selection-simple model.

or with an SQL statement:

Alter database name set recovery simple

----------------------------------------

The third approach perfectly solves my problem, it's a delightful day! And after the database was installed, it was a good thing to be able to deploy the project, hopefully everything goes well.