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
Log file shrunk
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
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
Do more with
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)
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)
>>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.
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.
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.
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.
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.
>>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.
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.
@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.
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.
>>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.
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial