January 4, 2013 at 12:59 am
hi ,
Dataabse in full recovery model and log backup is happeneing every 30 minutes and full backup every day .
We find huge available free space is available in log file .(database-->tasks-->shrink --.>files-->log )
can i shrink the log file ??
Thanks
lavanya
January 4, 2013 at 1:57 am
Yes you can, but first ask yourself if you really want/need this? Log growing is a very IO expensive process, and have huge impact on performance.
January 4, 2013 at 3:51 am
I would first check the used space of the log just before a backup is taken for a day, then you can see what space is actually used in a 30 minute window.
Say I have a log sized at 100GB, but in a 30 minute window only 1GB of that log is ever used, then I can safely say that 99GB of the log is not used in that 30 minute period.
I wouldnt shrink the log to 1GB, I would leave some wiggle room, so shrink to about 5GB and see how it goes, if the log increases again over time, then there is a need for the log to be that big.
But if in a 30 minute window the used log space ran up to 90GB I would leave the log file at 100GB as having to increase the log file from 5GB to 90GB will be a costly process.
It sounds like you had a run away process which consumed to much log space, or didnt have transaction log management in place for a while to make the log grow that big.
This will take some investigation before saying if you can or cannot shrink the file.
Stairway to Transaction Log Management - http://www.sqlservercentral.com/stairway/73776/
Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/
Why is my transaction log full - http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Accidental DBA Guide - Chapter 8 - Large or Full Transaction Log - http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply