shrinking log files after full database backup

stupy1 · March 3, 2006 7:58AM

Hi,

When we used to use standard sql server maintenance plans to backup our databases the jobs would shrink the log files after a full backup. I don't see this happening in SQL Backup. Am I missing this feature or has it just not been included yet?

Otherwise, I will create a separate job to shrink the logs.

thanks,
Justin

ps-love the software

petey · March 3, 2006 12:02PM

No, there isn't an option in SQL Backup to shrink the log file after a trx log backup.

Brian Donahue · March 3, 2006 4:15PM

Hello,

There is an auto-shrink property that you can set on the database properties, but there are a number of reasons why your logs still wouldn't shrink.

Microsoft do provide a method to shrink logs with the DBCC SHRINKFILE command:
http://msdn.microsoft.com/library/defau ... c_8b51.asp

stupy1 · March 3, 2006 11:10PM

Thanks for the replies.

Do you have plans to include a shrinking feature in any new version?

I think this would be very helpful to a lot of DBAs out there by not having to write our own little jobs for this. Shrinking a log file is a natural next step after a full database backup.

paulppp2004 · March 6, 2006 9:08AM

Does the last version of Red-Gate supposed to shrink transaction logs after you do a translation log backup? It's not doing it for me. Also, the transaction log, trx, backup is 0 bytes. Could that be right? Could you point me to the syntax for transaction log backup? I imagine I'm doing it wrong since it's 0 bytes.

petey · March 6, 2006 10:13AM

No, SQL Backup does not shrink trx log files. The 'remove inactive entries from transaction log' option refers to the use of the NO_TRUNCATE option.

The syntax for a trx log backup is similar to the T-SQL syntax e.g.

execute master..sqlbackup '-sql "BACKUP LOG pubs TO DISK = [G:\Backups\pubs\<AUTO>]" '

You could also the trx logs of multiple databases e.g.

execute master..sqlbackup '-sql "BACKUP LOGS [northwind, pubs] TO DISK = [G:\Backups\<database>\<AUTO>]" '

randyj · May 8, 2006 1:46PM

Sorry, I'm not understanding this -
Are we saying that after eitehr a full DB backup and/or after a trx log backup, the trx logs are NOT truncated ?

If so this seems a pretty basic piece of functionality that SQL Backup lacks. I mean, as a DB operator, I am accustomed to log maintenance being taken care of by the backup procedure. Do we now need to create yet another job to do log cleanup ?

petey · May 8, 2006 10:09PM

If your definition of truncation is 'remove inactive entries from the trx log so that new entries can be written in it's place', then yes, the SQL Server backup process automatically does this for you when you perform a trx log backup. Note that a full database backup does not truncate the log.

If your definition of truncation is 'shrink the transaction log files after a trx log backup', then no, neither SQL Server nor SQL Backup performs this automatically. The rationale is because if the trx log file grows to 'x' size, it can be expected to grow again to 'x' size even if you shrink it after a backup.

See

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

for a discussion on this.

If enough users ask for an post-backup shrinkfile option, we would consider adding it in version 5.

shrinking log files after full database backup

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk