Provide a sophisticated method of compressing the log and database files as follows:
1. Clear the Log
DUMP TRANSACTION Library name with NO_LOG
2. Truncate the transaction log:
BACKUP LOG database name with NO_LOG
3. Shrink the database file (if not compressed, the database file does not decrease
Enterprise Manager--right-click the database you want to compress--all tasks--shrink the database--Shrink the file
--Select Log file--select shrink to XXM in the shrink mode, here will give an allowable shrinkage to the minimum m number, directly enter this number, OK.
--Select the data file--choose Shrink to XXM in the contraction mode, here will give a allowable shrinkage to the minimum m number, directly enter this number, OK.
You can also use SQL statements to complete
--Shrinking 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. In order to minimize the log file (if SQL 7.0, this step can only be done in Query Analyzer)
A. Detach the database:
Enterprise Manager--server--database--right--detach database
B. Delete the log file from My Computer
C. Additional databases:
Enterprise Manager--server--database--right--Attach Database
This method will generate a new log with a size of only more than 500 k
or in code:
The following example detaches pubs and then attaches a file in pubs to the current server.
A. Separation
E X e C sp_detach_db @dbname = ' pubs '
B. Deleting a log file
C. Re-attach
E X e C sp_attach_single_file_db @dbname = ' pubs ',
@physname = ' C:Program filesmicrosoft SQL servermssqldatapubs.mdf '
5. In order to automatically shrink in the future, do the following settings:
Enterprise Manager--server--right-click Database-Properties--Options--select "Auto Shrink"
--sql Statement Setup Method:
E X e C sp_dboption ' database name ', ' autoshrink ', ' TRUE '
6. If you want to not let it grow too large in the future
Enterprise Manager--server--right-click Database--Properties--transaction log
--Limit file growth to XM (x is the largest data file size you allow)
How to set the--sql statement:
ALTER DATABASE name modify file (name= logical file name, maxsize=20)
Special attention:
Please follow the steps, do not take the previous steps, please do not follow the steps
Failure to do so may damage your database.
Generally not recommended to do 4th, 62 steps
The 4th step is unsafe, potentially damaging the database or losing data
6th step if the log reaches the upper limit, subsequent database processing will fail and cannot be resumed until the log is cleaned up.
In addition to provide a more simple method, I have tried, recommend everyone to use.
A simpler approach:
1. Right-built Database Properties window--Failure restore model--set as simple
2. Build database All Tasks--shrink Database
3. Right-built Database Properties window--Failure restore model--set to bulk-logged
http://www.bkjia.com/PHPjc/631064.html www.bkjia.com true http://www.bkjia.com/PHPjc/631064.html techarticle first, provide a complex method to compress the log and database files as follows: 1. Emptying log DUMP TRANSACTION library name with NO_LOG 2. Truncate transaction log: BACKUP log database name with no_log ...
