shrinking log files after full database backup

stupy1stupy1 Posts: 7
edited May 8, 2006 10:09PM in SQL Backup Previous Versions
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

Comments

  • peteypetey Posts: 2,358 New member
    No, there isn't an option in SQL Backup to shrink the log file after a trx log backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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
  • 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.
  • 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.
    PP
  • peteypetey Posts: 2,358 New member
    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>]" '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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 ?
  • peteypetey Posts: 2,358 New member
    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
    for a discussion on this.

    If enough users ask for an post-backup shrinkfile option, we would consider adding it in version 5.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.