/*--Special Note Please follow the steps, do not do the next steps without doing the previous steps or you may damage your database. It is generally not recommended to do steps 4 and 6 Step 4 is not safe and may damage your database or lose data Step 6 If the log reaches its limit, subsequent database processing will fail and you will not be able to recover until the log is cleaned up. --*/ -- All the library names below refer to the library name of the database you want to process 1. Empty the log DUMP TRANSACTION library name WITH NO_LOG 2. Truncate the transaction log: BACKUP LOG library name WITH NO_LOG 3. Shrink the database file (if you do not compress, the database file will not be reduced) Enterprise Manager - right-click on the database you want to compress the Database - All tasks - Shrink the database - Shrink the file - - Select the log file - in the contraction mode, select shrink to XXM, here will be given a minimum allowable shrinkage to the number of M, directly enter this number, OK on it - Select the data file - in the contraction mode, select shrink to XXM, here will be given a minimum allowable shrinkage to the number of M, directly enter this number, OK on it You can also use SQL Server to shrink the database file. You can also use the SQL statement to complete --- shrink the database DBCC SHRINKDATABASE (library name) --- shrink the specified data file, 1 is the file number, you can query through this statement: select * from sysfiles DBCC SHRINKFILE (1) 4. In order to maximize the shrinkage of the log file (if the sql 7.0, this step can only be done in the query analyzer) a. Separate the database: Enterprise Manager - Server - Database - right-click - Separate the database b. Delete the LOG file in my computer c. Attach the database: Enterprise Manager - Server - Database - right-click - Attach the database This method will generate a new LOG, the size of the only more than 500K or with code: The following example detaches pubs and then attaches a file from pubs to the current server. a. Detach EXEC sp_detach_db @dbname = 'library name' b. Delete the log file c. Attach again EXEC sp_attach_single_file_db @dbname = 'library name', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ library name.mdf' 5. In order to be able to automatically shrink in the future, do the following settings: Enterprise Manager - Server - right-click on the database - Properties - Options - select "Autoshrink" - SQL statement set the way: EXEC sp_dboption 'library name', 'autoshrink', 'TRUE' 6. If you want to prevent it from growing too large in the future Enterprise Manager - Server - Right click on the database - Properties - Transaction Log - Limit file growth to xM (x is the maximum size of the datafiles you allow) - SQL statement to set this: alter database library modify file(name=logical filename

)