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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.