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.

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 !

Sirees

Shrink the log file

http://support.microsoft.com/?id=272318

ASKER CERTIFIED SOLUTION

MikeWalsh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeWalsh

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.