We help IT Professionals succeed at work.

Backup tran with no_log and Shrinking the Log File

BetsyV
BetsyV asked
on
Medium Priority
1,617 Views
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.
Comment
Watch Question

Author

Commented:
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 !
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.