SQL Transction Log Not Truncating

Posted on 2011-10-22
Last Modified: 2012-05-12
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?

Question by:chasmx1
    LVL 23

    Accepted Solution

    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.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 2

    Expert Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
    This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now