/*--pay attention
Please follow the steps, do not perform the previous steps, please do not do the following steps, otherwise it may damage your database.
Generally, it is generally not recommended to do 4th, 6 steps in step 4 is not safe. It is possible to damage the database or lost data. Step 6 If the log reaches the upper limit, the subsequent database processing will fail, after the logging log can be recovered. - * /
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)
