• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1563
  • Last Modified:

Backup tran with no_log and Shrinking the Log File

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.
  • 2
1 Solution
BetsyVAuthor 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.
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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now