asked on 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
Contact ManagementMicrosoft SQL Server 2005Enterprise Software
Last Comment
daiwhyte
>>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.
ASKER
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.
ASKER
@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.
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you.
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)