- Assumption TEST2 is the database name
When the log is very big
Method First, this method is applicable to 7.0 and 2000.1, execute in the query analyzer: exec sp_detach_db 'db_name'2, rename the physical file XXX_log.ldf of the log in my computer. 3, execute in the query analyzer: exec sp_attach_single_file_db 'db_name', 'C: / Program Files / Microsoft SQL Server / MSSQL / DATA / DB_NAME.MDF'4, if the previous step is successful, remove the file after the name of the change in step 2 is deleted . If the last step is not successful, change it back to the original file name, attach the database to the server with sp_attach_db, and then use method two.
Method 2 6.X Dump Transaction Test2 with no_logdump Transaction Test2 with truncate_only executes the above statement multiple times until the log is reduced. 7.0 and 2000 Backup log test2 with no_logbackup log test2 with truncate_onlydbcc shrinkDatabase (TEST2) Executes the above statement multiple times until the log file is reduced.
The above method is not ruled by the following methods.
Method 3: - 6.x and 7.0 are changed to the log in truncation mode, and the recovery model in 2000 is changed to simply restore EXEC SP_DBOPTION 'TEST2', 'Trunc. Log on chkpt.', 'On' - 7.0 and 2000 Set to automatic shrinkage, no execution in 6.x. EXEC SP_DBOPTION 'TEST2', 'AutoHrink', 'on' is usually used to test the environment.
Method 4: - 7.0 Change to the log is not in the truncation mode, the recovery model in 2000 is changed to completely restore the exec sp_dboption 'TEST2', 'Trunc. Log on chkpt.', 'Off' - 7.0 and 2000 set to Automatic Contracted, no execution in 6.x. EXEC SP_DBOPTION 'TEST2', 'AutoShrink', 'On' establishes a job, one full database backup every half an hour, once a day. 7.0 and 2000: After the log is shrunk to normal, set the AutoShrink option to OFF. Usually used in a real environment.
Setting the AutoShrink option to open status in the product chemical system is not wise (unless you really need this), this is because when your system is busy with other tasks, the AutoShrink option may start at the same time, reducing The system is running speed. However, this option is a very effective measures for those desktops or remote systems that are more valuable to those database administrators and database sizes that are likely to exceed the control range without notice.
Shrinkage business log
In the following cases, the physical size of the log file will be reduced: * When executing the DBCC ShrinkDatabase statement. * When performing a DBCC ShrinkFile statement of a reference log file. * The automatic shrinkage occurs.
The log shrinkage is dependent on the initial log truncation operation. The log truncation does not reduce the size of the physical log file, but reduces the size of the logical log and will not be able to mark the virtual log of any part of the logical log as inactive. Log Shrinking Action Deletes enough inactive virtual logs to reduce the log file to the required size.
Control the size of the transaction log as follows: * When the log backup sequence is maintained, the scheduled backup LOG statement occurs, so that the transaction log is not growing to more than the expected size. * Specify simple recovery mode when the log backup sequence is not maintained. For details, please refer to the MS SQL Server 2000 Book: Directory -> SQL Server Architecture -> Database Menge -> Physical Database -> Transaction Log Arrangement -> Contract Transaction Log Directory -> SQL Server Architecture -> Database Menge -> Physical Database -> Transaction Log Arrangement -> Truncated Transaction Log
