SQL Transction Log Not Truncating

My Transaction log is 10 times the size of my database.  I'm doing a separate backup of the transaction log only after a full backup but the log refuses to truncate.   How can I force the log to truncate?

Who is Participating?
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Follow these steps
1. Execute this command against your database
Dump transaction <<your dbname>> with no_log
2. Right-Click database - Shrink Files - Select 'Reorganize pages before releasing usused space' - Enter 'Shrink File to ' - 5 - Click OK

Please let me know the status.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that transaction log backups should not be done once per day, after a full backup, but something like once per hour of even more frequently.

what you are seeing is the result of just doing 1 log backup, presumably.
If you look at the properties of the database on the FILE area, what is the initial size of the transaction log? THe shrink will not reduce it smaller than the initial size that is listed.
When i have encountered this, i have been forced to detach the database, rename the LDF file to something else (like XDF) and then attach the database - SQL will automatically create a new log file that is the minumim size of a file allowed.
All Courses

From novice to tech pro — start learning today.