Microsoft:SQL:Shrink transaction log

From My Notepad
Revision as of 16:01, 25 August 2008 by Admin (talk | contribs) (Shrink transaction log)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Shrink transaction log

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE

To shrink the transaction log of a database do the following:

  1. Change the recovery model to Simple.
  2. Run the following SQL command/query to shrink the file to 2MB: DBCC SHRINKFILE(database_name_log, 2)
    1. If the target size is not reached, proceed with the following step.
    2. Run the following SQL command/query: BACKUP LOG database_name WITH TRUNKCATE_ONLY
    3. Re-run the DBCC SHRINKFILE command.
  3. Change the recovery model back to Full.