Shulou(Shulou.com)06/01 Report--

Method 1:

1. Open the query analyzer and enter the command

BACKUP LOG database_name WITH NO_LOG

2. Open the enterprise manager-- right-click the database to be compressed-- all tasks-- shrink the database-- shrink the file-- select the log file-- select shrink to xxm in the shrink mode. Here, you will give a minimum number of m allowed to shrink. Enter this number directly and make sure.

Method 2: (I try to be very effective)

Set checkpoints and automatically truncate logs

In general, the shrinking of the SQL database can not greatly reduce the size of the database. Its main function is to reduce the size of the log. This operation should be done regularly to prevent the database log from being too large.

1. Set the database mode to simple mode: open SQL Enterprise Manager and click Microsoft SQL Server-- > SQL Server group in the console root directory-- > double-click to open your server-- > double-click to open the database directory-- > Select your database name (such as user database cwbase1)-- > then right-click to select Properties-- > Select options-- > Select "simple" in the failure recovery mode. Then press OK to save.

2, right-click on the current database, look at all the tasks in the shrinking database, generally inside the default settings do not need to be adjusted, directly click to determine.

3. After shrinking the database, it is recommended to reset your database properties to standard mode. The operation method is the same as the first point, because logs are often an important basis for restoring the database in some abnormal cases.

Method 3: delete the log file

This method has a certain risk, because the log file of sql server is not immediately written to the main file of the database, if it is not handled properly, it will cause data loss.

1. Please disconnect all database connections before operation.

2. Separate the database

Detach Database: enterprise Manager-> Server-> Database-> cwbase1- > right-> detach Database

After detaching, the cwbase1 database is deleted, but the data files and log files are retained.

3. Delete log physical files

Delete the LOG physical file, and then attach the database: enterprise Manager-> Server-> Database-> right-click-> attach database.

This method generates a new log with a size of only more than 500k.

Note: Xiao Yun recommends using the first method. Before operation, please make sure that all operators have launched the system and disconnected the database.

Please make a good data backup before the above operation!

1.sql server 2005 clear log statement:

Dump transaction database name with no_log

Backup log database name with no_log

Dbcc shrinkdatabase (database name)

2.sql server 2008 clear log statement:

Sp_dboption database name, "trunc. Log on chkpt.", true

Checkpoint

Sp_dboption database name, "autoshrink", true

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.