shrink transaction log back to default size

colby

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

petey

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.

colby

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.

petey

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.

colby

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.

Eddie D

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