shrink transaction log back to default size

colbycolby Posts: 30
edited October 17, 2008 12:39PM in SQL Backup Previous Versions
When a database is created, the default size of the database is 2MB and log is 1MB. Under the full recovery model, I know that the transactional backing up of the log only removes the unused part of the log but the log size remains unchanged. So over a period of very active growth the log can exceed the size of the database. The log now has 99% of unused space. So assuming you have hourly transactional log backups the size of the log continues to grow with more and more unused space.

How does Red Gate recommend to get the log back to 1MB?!!!!!!

Is there a solution with each hourly backup job? What other options are there? dbcc shrinkfile

Thanks

Colby
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    So assuming you have hourly transactional log backups the size of the log continues to grow with more and more unused space.
    Could you please clarify what you mean by that? When you back up the transaction log, SQL Server will mark the space occupied by the backed up transactions as reusable, as long as the transactions are not part of the active portion of the log, and the transactions have been replicated in a transaction replication setup. Thus, it is not likely that the transaction log will grow indefinitely.

    Reducing the physical size of the transaction log files to an unrealistic size is not a recommended option. This is because the growth may be due to normal database activity, which will eventually grow the transaction log to that size again, causing the file to fragment in the process. The exception is when you had a one-off event e.g. mass-loading data from an external source. In this case, shrinking the transaction log to a reasonable size is appropriate. See here for more details.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • colbycolby Posts: 30
    To the details: I have a database that is 23 GB with a log that is 73GB and we do backups with transactional logging on the hour, differentinals 6 daya a week and fulls 1 day a week.

    I did the DBCC LOGINFO('staging') and found some 474 rows of the below. How do I remove these when I have "Remove inactive entries from transaction log" checked. Is there another option to remove the below rows.

    FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
    2 253952 8192 15508 0 128 0
    2 253952 262144 15509 0 128 0
    2 253952 516096 15510 0 128 0
    2 278528 770048 15511 0 128 0
    2 262144 1048576 15864 0 128 18000000013600180
    2 262144 1310720 15865 0 128 18000000022300388
    2 262144 1572864 15738 0 64 19000000001600453
    2 262144 1835008 15739 0 64 19000000015200406
    2 262144 2097152 15744 0 64 19000000031600456
    2 262144 2359296 15745 0 64 20000000006600262
    2 262144 2621440 15746 0 64 20000000017100339
    2 327680 2883584 15747 0 64 20000000030200442
    2 327680 3211264 15748 0 64 21000000013600131
    2 393216 3538944 15749 0 64 21000000030900396
    2 393216 3932160 15750 0 64 22000000013600300
    2 458752 4325376 15530 0 128 22000000038900356
    2 253952 4784128 15531 0 128 23000000028100342
    2 270336 5038080 15532 0 128 23000000028100342
    2 253952 5308416 15533 0 128 24000000013600440
    petey wrote:
    So assuming you have hourly transactional log backups the size of the log continues to grow with more and more unused space.
    Could you please clarify what you mean by that? When you back up the transaction log, SQL Server will mark the space occupied by the backed up transactions as reusable, as long as the transactions are not part of the active portion of the log, and the transactions have been replicated in a transaction replication setup. Thus, it is not likely that the transaction log will grow indefinitely.

    Reducing the physical size of the transaction log files to an unrealistic size is not a recommended option. This is because the growth may be due to normal database activity, which will eventually grow the transaction log to that size again, causing the file to fragment in the process. The exception is when you had a one-off event e.g. mass-loading data from an external source. In this case, shrinking the transaction log to a reasonable size is appropriate. See here for more details.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The option 'Remove inactive entries from transaction log' just allows SQL Server to truncate the transaction log, but does not physically shrink the file. This is actually the default behaviour when you back up the transaction log. If you uncheck the option, SQL Backup will back up the transaction log using the NO_TRUNCATE option.

    With regards to your transaction log file, you'll need to determine if 73 GB is 'normal'. E.g. you could be reindexing the entire database periodically, which would potentially grow the transaction log to that size. In that case, 73 GB is normal, and shrinking it is useless as it'll just grow again during the next reindexing process.

    If however you think 73 GB is an abnormal size, you could use the DBCC SHRINKFILE command to physically reduce the size. You may need to run that command once, back up the transaction log, and run it again to successfully shrink the file. See here for details.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • colbycolby Posts: 30
    Yes 73GB log file is to large. Please provide an example on how I would use redgate to run DBCC shrinkfile "command once, back up the transaction log, and run it again to successfully shrink the file" without losing transaction. Using 'with truncate_only' is not an option.

    Thanks for your help.

    Colby

    petey wrote:
    The option 'Remove inactive entries from transaction log' just allows SQL Server to truncate the transaction log, but does not physically shrink the file. This is actually the default behaviour when you back up the transaction log. If you uncheck the option, SQL Backup will back up the transaction log using the NO_TRUNCATE option.

    With regards to your transaction log file, you'll need to determine if 73 GB is 'normal'. E.g. you could be reindexing the entire database periodically, which would potentially grow the transaction log to that size. In that case, 73 GB is normal, and shrinking it is useless as it'll just grow again during the next reindexing process.

    If however you think 73 GB is an abnormal size, you could use the DBCC SHRINKFILE command to physically reduce the size. You may need to run that command once, back up the transaction log, and run it again to successfully shrink the file. See here for details.
    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Unfortunately you cannot set the DBCC Shrink File via Red Gate SQL Backup.

    This Knowledge Base article explains what you to perform:

    http://www.red-gate.com/supportcenter/C ... 000076.htm

    I hope this helps you.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.