|
If for some reason
the transaction log became too large, like 20GB for a 3GB database, or contains leftover from
various processes such as replication, you can empty it quickly the following
ways.
A. Exit users, Backup database
B. Change database to SIMPLE recovery model
C. In SSMS Object Explorer Perform Shrink Database Files for log
You can also use script:
USE [AdventureWorks2008]
GO
DBCC SHRINKFILE (N'AdventureWorks2008_Log' , 0, TRUNCATEONLY)
GO
The following method works even if dbcc shrinkdatabase or dbcc shrinkfile fails :
1. Exit users
2. Check for open transactions with dbcc opentran - resolve if any
3. Backup the database (full backup) with verification
4. Detach the
database in Object Explorer
5. Rename the
transaction log file (discard later e.g. in a week)
6. Attach the
database in OE (remove "not found" .ldf file)
7. Perform dbcc checkdb
8. Backup database with verification
9. Open database for users
An empty log file
will be created upon attach. Database should be fully operational.
Related articles:
How to: Shrink a Database (SQL Server Management Studio)
Recover from a full transaction log in a SQL Server database
http://sqlusa.com/bestpractices2005/shrinklog/
|