Database log Shrink Size

/*--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.
--*/

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
EXEC sp_detach_db @dbname = ' pubs '

B. Deleting a log file

C. Re-attach
EXEC sp_attach_single_file_db @dbname = ' pubs ',
@physname = ' c:\Program Files\Microsoft SQL Server\mssql\data\pubs.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:
EXEC 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)





SQL is sure to record the log, you may not need it, but the SQL itself processing also requires the log file.

In SQL, you can use the following methods to reduce the log:
1. Set the database recovery model to simple (minimal logging)
ALTER DATABASE name set recovery simple

2. Automatically shrink the settings database log
ALTER DATABASE library name set Auto_shrink on

3. Create a job and periodically execute the following statement to clean up the log:
Backup Log library name with NO_LOG