Link to home
Create AccountLog in
Avatar of BetsyV
BetsyV

asked on

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.
Avatar of BetsyV
BetsyV

ASKER

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 !
ASKER CERTIFIED SOLUTION
Avatar of MikeWalsh
MikeWalsh
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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.