1. Empty the log
DUMP TRANSACTION library name WITH NO_LOG
2. Truncate the transaction log:
The BACKUP LOG database name is WITH NO_LOG
3. Shrink database files (If you don't compress them, the database files won't shrink.)
Enterprise Manager - Right-click on the database you want to compress - All Tasks - Shrink Database - Shrink File
--Select log file--Select shrink to XXM in the shrink method, here will give a minimum M number that can be shrinked, enter this number directly, and you can confirm
--Select data file--Select shrink to XXM in the shrinkage method, here will give a minimum M number that can be shrinked, enter this number directly, and you can confirm
It can also be done with SQL statements
--Shrink the database
DBCC SHRINKDATABASE (Customer Profile)
--Shrink the specified data file, 1 is the file number, which can be queried by this statement: select * from sysfiles
DBCC SHRINKFILE(1)
4. To maximize the minification of log files (in the case of SQL 7.0, this step can only be done in the query analyzer)
a. Separating Databases:
Enterprise Manager - Server - Database - Right Click - Detach Database
b. Delete the LOG file in my computer
c. Additional databases:
Enterprise Manager - Server - Database - Right Click - Attach Database
This method will generate a new LOG, which is only more than 500K in size
Or use code:
The following example detaches the pubs and then attaches a file from the pubs to the current server.
a. Separation
E X E C sp_detach_db @dbname = 'pubs'
b. Delete log files
c. Additional Additions
E X E C sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
5. In order to shrink automatically in the future, do the following settings:
Enterprise Manager - Server - Right Click on Database - Properties - Options - Select "Auto Shrink"
--SQL statement setting:
E X E C sp_dboption 'database name', 'autoshrink', 'TRUE'
6. If you want to keep it from growing too much in the future
Enterprise Manager - Server - Right-click Database - Properties - Transaction Log
--limit file growth to xM (x is the maximum data file size you allow)
--SQL statement setting:
alter database database name modify file(name=logical filename,maxsize=20)
Special Note:
Please follow the steps, do not do the next steps without the previous steps
Otherwise it may damage your database.
It is generally not recommended to do steps 4 and 6
Step 4 is not secure and may damage the database or lose data
Step 6 If the log reaches the upper limit, the database processing will fail in the future and can only be restored after the log is cleaned.
In addition, it provides a simpler method, I have tried it many times, and I recommend you to use it.
Easier way:
1。 Right: The Database Properties window - Failback Model - is set to Simple
2。 Right-build all tasks of the database - shrink the database
3。 Right: Build the database properties window - Failback model - set to Large Volume Logging |
Posted on 2/11/2015 1:25:53 PM
|
|
|
