Ms SQL Server transaction log compression and Deletion

Method 1:

Step 1:
Backup log database_name with no_log
Or backup log database_name with truncate_only
-- No_log and truncate_only are synonymous here. You can execute any sentence at will.

Step 2:
1. compress all data and log files of a specific database and execute:
DBCC shrinkdatabase (database_name, [, target_percent])
-- Database_name is the name of the database to be shrunk; target_percent is the percentage of available space in the database file after the database is shrunk.

2. compress the data or log files in a specific database and execute
DBCC shrinkfile (file_id, [, target_size])
-- File_id is the ID of the file to be shrunk. To obtain the file ID, use the file_id function or search sysfiles in the current database; target_size indicates the size of the desired file in megabytes (expressed in integers ). If not specified, DBCC shrinkfile reduces the file size to the default file size. Both DBCC can contain the notruncate or truncateonly parameter. For more information, see online help.

Method 2:

Step 1:
Back up the entire database for emergency purposes.

Step 2:
After the backup is complete, run the following statement in query Analyzer:
Exec sp_detach_db yourdbname, true
-- Remove the registration information of this dB in MSSQL

Step 3:
Delete the log file or remove the log file from the directory where the log physical file is located.

Step 4:
Execute the following statement in query Analyzer:
Exec sp_attach_single_file_db yourdbname, 'd:/MSSQL/data/yourdbname_data.mdf'
-- Register the database as a single file. If yes, MSSQL automatically generates a K log file for the database.
 
Method 3:

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

2. All tasks-> database Separation

3. Go to the database file storage directory and delete the muonline_log.ldf file. Just in case, you can copy it out.

4. enterprise Manager-> attach the database and select muonline. At this time, you will see that the log file is a cross. It doesn't matter. Continue, at this time, the database will prompt you whether to create a new database without logs. OK.

5. Remember to reset the settings after the database is reappended.

If you do not want it to become larger later:

Use in SQL2000:

Right-click the database and choose Properties> Options> fault recovery> model> select simple model.

Or use an SQL statement:

Alter database name set recovery simple