How to compress the log file of the database

zhaozj2021-02-16  66

- Compressed log and database file size

/ * - Special pay attention Please follow the steps, not in front of the steps, please do not do the following steps may not damage your database.

1. Clear Log Dump Transaction Database name with no_log

2. Truncate the transaction log: Backup log database name with no_log

3. Shrinkage database files (if not compressed, database files do not reduce Enterprise Manager - Right-click on the database - All Tasks - Shrink Database - Shrink File - Select Log File - In Shrink Mode The choice to shrink to XXM, here will give a minimum M number that is allowed to shrink, enter this number directly, determine it - select data file - Select shrinkage to XXM in the shrink mode, here will give a permission The minimum number of shrinks is contracted, enter this number directly, it is ok.

You can also use the SQL statement to complete - shrink database DBCC ShrinkDatabase (customer data) - shrink specified data file, 1 is the file number, you can query: select * from sysfiles dbcc shrinkfile (1)

4. In order to maximize the log file (if it is SQL 7.0, this step can only be performed in the query analyzer) a. Separate Database: Enterprise Manager - Server - Database - Right - Separate Database

b. Delete log files in my computer

c. Additional Database: Enterprise Manager - Server - Database - Right - Right - Additional Database

This method will generate new logs, only more than 500 k

Or use code: The following example separates the PUBS and attached one of the files in the public to the current server. A. Separate EXEC SP_DETACH_DB @dbname = 'PUBS'

b. Delete the log file c. Additional EXEC SP_ATTACH_SINGLE_FILE_DB @dbname = 'PUBS', @physname = 'c: / program files / microsoft sql server / mssql / data / pubs.mdf'

5. In order to automatically shrink automatically, do the following: Enterprise Manager - Server - Right-click Database - Properties - Options - Select Auto Shrink - SQL Statement Settings: EXEC SP_DBOPTION 'Database Name', ' AutoShrink ',' True '

6. If you want to get the log growth too large, you don't want to grow too large enterprise manager - Server - Right-click Database - Property - Transaction Log - Limit the file growth to XM (x is your maximum data file size allowed)

--SQL statement setting mode: Alter Database Database name modify file (name = logical file name, maxsize = 20)

(Source: http://community.9cbs.net/expert/topic/3039/3039451.xml? Temp = .852627)

转载请注明原文地址:https://www.9cbs.com/read-15488.html

New Post(0)