Log file shrunk

daiwhyte
daiwhyte used Ask the Experts™
on
Hi,

Each night, I ran a transaction log backup and shrink of my main database table. At first, i didnt make much difference to the log file size (gets up to around 3gb in size). Last week, the log file shrunk to 90mb and I have no idea why. The log file is now starting to grow in size and is currently up to about 1.4gb.

Why would this happen? Not that Im complaining but it has made a difference to my log shipping process which has not failed once since the log file has shrunk. Would like to know how I can keep the log file small since the log shipping is far more robust.

Thanks
DW
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Make sure full backup and trans log backup are successful - and there is enough space to accommodate backups.
Occationally run T-SQL script to shrunk databases and log files.

Example Script to shrink msdb:

USE "msdb"
DBCC SHRINKFILE ("msdbdata", TRUNCATEONLY)
DBCC SHRINKFILE ("msdblog", TRUNCATEONLY)
Top Expert 2012

Commented:
>>shrink of my main database table<<
That is a very bad idea for two reasons
1. Index fragmentation.
2. It defeats the whole point of using the Full Recovery Model.

Either:
1. Do frequent transaction log backups or if you are unable to do that then
2. Change to Simple Recovery Model.
Top Expert 2012

Commented:
Correction not Index fragmentation but rather Transaction log fragmentation.  You can easily confirm tis by doing:
DBCC LOGINFO()
I suspect you must have hundreds of rows.

Also, since you appear to be doing log shipping, you have no choice but to increase the number of Transaction Log backups.  I would suggest doing it once every hour and monitor that for the next few weeks.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Ive come in this morning and checked the log file and its 30mb so my overnight backup of the transaction logs looks like its working. Do I need to keep the transaction log backups? Im only running the process to keep the log file size down which aides my log shipping process.

Top Expert 2012

Commented:
>>Ive come in this morning and checked the log file and its 30mb so my overnight backup of the transaction logs looks like its working.<<
There seems to be some misudnerstanding here:  Backups do not shrink the Transaction Log and we can only hope that you are still not doing a DBCC SHRINKFILE and/or you do not have Auto-shrink on.  Also I suspect that 30mb is far too small for a Transaction Log file.

>>Do I need to keep the transaction log backups? <<
Only if you want to sleep soundly at night.  You shoud keep them as long as you think you may need a point-in-time restore.

Author

Commented:
@acperkins

Thanks for responding.

When I said backup, I meant to say Transaction Log Backup, it happens each morning at 1am.

With regards to needing a point in time restore, we dont need to do this. If we lost the db, we would be happy with the previous nights backup to restore our DB.
Top Expert 2012

Commented:
>>If we lost the db, we would be happy with the previous nights backup to restore our DB. <<
Than your solution is quite simple (no pun intended) just change your Recovery Model from Full to Simple and you will not have to do any more Transaction Log backups.

Also, make sure the Auto-Shrink option is not on and you are no longer scheduling a DBCC SHRINKFILE task.

Author

Commented:
I need the db to be in FULL to facilitate log shipping? The db used to be in Simple mode but Im sure I switched it to FULL for the log shipping.

Top Expert 2012
Commented:
That is true, I overlooked that detail.  Here is what you need to do then:
1. Make sure Auto-Shrink is off.
2. Do not schedule any DBCC SHRINKFILE
3. Do frequent Transaction Log backups.  For example, you can do one every hour or more frequently as necessary.

This way your Transaction Log does not increase in size.

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial