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.
1. Empty the Log
DBCC Shrinkfile (library name _log,0)
DUMP TRANSACTION Library name with NO_LOG
2. Truncate the transaction log:
If "File Vault name _log ' could not be found in Sysfiles" appears.
DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. ”
Then use this SQL operation
BACKUP LOG Library name with NO_LOG
DBCC Shrinkfile (2,0)
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
A, select the log file-shrink the file to, here will give a allowed to shrink to the minimum number of M, OK Yes
b, select the data file-shrink the file to, here will give a allowable contraction to the minimum number of M,, OK.
You can also use SQL statements to complete
--Shrinking database
DBCC shrinkdatabase (library name)
--Shrink the specified data file, 1 is the file number, which can be queried by this statement: SELECT * fromsysfiles
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 = ' Library name '
B. Deleting a log file
C. Re-attach
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 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 ' library 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 library name Modifyfile (name= logical file name, maxsize=20)
The transaction log for the database is full. To find out why you cannot reuse space in the log, see the Log_reuse_wait_desc column in sys.databases