Ms SQL performance is very good, but the database has been used for a period of time, the database has become very large, the actual amount of data is not large. It is generally caused by database logs! The growth of database logs can reach several hundred MB.
The cost of MSSQL virtual hosts on the Internet is also high. To avoid database overuse, You Have To Compress database logs or delete database logs.
Next, I will introduce a method.
1. Open the Enterprise Manager
2. Open the database to be processed
3. Choose tools> SQL query analyzer.
4. In the input window, enter:
Copy codeThe Code is as follows: dump transaction [database name] WITH NO_LOG
Backup log [database name] WITH NO_LOG
Dbcc shrinkdatabase ([database name])
Click to execute! In this way, the database operation is successful.
Methods In the program:
Compressing database logs
-- 1. Clear logs
Exec ('dump TRANSACTION ['+ @ dbname +'] WITH NO_LOG ')
-- 2. truncate transaction logs:
Exec ('backup LOG ['+ @ dbname +'] WITH NO_LOG ')
-- 3. compress the database file (if it is not compressed, the database file will not be reduced
Exec ('dbcc SHRINKDATABASE (['+ @ dbname +']) ')