Full recovery model shrink database

  • 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

  • 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.

  • 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