Backup tran with no_log and Shrinking the Log File

Posted on 2006-03-24
Last Modified: 2008-02-01
I have a fairly large database  that I thought was being well maintained, but the transaction log has grown large (7GB).  We run a transaction log backup hourly.  Each night it runs the following commands:

backup tran dbname with no_log
backup database dbname to dbname_backup with init

There are further commands in the job to reindex the database, and then shrink it with:  

dbcc shrinkdatabase (dbname, 10)

These commands were provided by the vendor of the software, and have been running well, but the size of the transaction log worries me.  I was thinking of adding a command to the nightly backup to shrink the log, but wanted some input first.  Also, if I do this, is it going to take a long time to shrink it down the first time?

Any advice is really appreciated.
Question by:BetsyV

    Author Comment

    One more point, to get the full picture, the SQL job that runs nightly also runs this command after the database backup, and other maintenance has run, the final step is the following:

    backup log dbname to dbname_backup_log with init

    Just in cse this helps to understand.

    Thanks again !
    LVL 20

    Expert Comment

    LVL 13

    Accepted Solution

    Betsy -

    You can shrink the log file each night if you are worried about free space. If the log file is not quickly growing out of bounds, it may not be a bad idea to just let the physicaly file stay large and take care of the backups to eliminate the usage of all that space.

    The physical file is the space holder for the logical data contained within the Transaction Log. If your app is busy with a lot of use, the physical file will probably grow each day with regular use, and if you keep shrinking it you may impact performance slightly by the users having to wait for the autogrow to grow the physical file for the log.

    If it is a big concern, you can always do your full backup that you do each night, and you could immediately after issue a BACKUP LOG dbname WITH TRUNCATE_ONLY Doing this eliminates the logical data on the file. The file stays at its current size, but it makes it less likely to grow more. Even though this file is being truncated each time a log backup completes, it doesn't hurt to explicitly pass this after the backup job.

    So if the file has been 7GB for a while, it should only really be a huge concern if space is a concern. If it just grew that way, maybe there was a surge in activity?

    Look up Transaction Log in SQL Server Books Online and search for "Backup Log" in the index of books online and choose the topic for truncating the log.
    LVL 13

    Expert Comment

    I'm sorry I see that you are doing regular Transaction Log backups and forgot the important caveat. Doing the Truncate_Only will invalidate your Transaction Log backup sequence. This could impact recovery. You should actually be fine to not to the backup log since you are doing them throughout the day.

    But just remember if you shrink the file, it will grow again and that file growth does incur some cost if high performance is a big concern.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now