asked
Need to Shrink transaction log
We have a database which is currently 250 GB in size. The data is close to 23 GB but the log has grown to **225 GB**. Database is in Full recovery and there were no transaction log backups due to which it has ballooned to this size (I know where it went wrong :(). Now there is just 20 GB left on the data drive and there is no space for taking a transaction log backup. If we change recovery model to simple and shrink the transaction log would it result in data loss ? Or is there a better way to shrink this overgrown log file ? Please help ! It's a little urgent :(
Changing to simple recovery will not result in data loss. However, it does mean that if you were to have any type of outage and wanted to restore the database to a point in time, you would absolutely be unable to. In your situation, I would change to simple recovery, truncate the log and shrink it. Shift it back to full recovery. Immediately take a full backup and then start taking log backups on a scheduled basis in compliance with your Recovery Point Objective (RPO).
There is another method to get the transaction log to reduce in size. I wouldn't suggest even doing this unless the criteria are already met that you have explained. 1. There is no current log chain. 2. You are already looking to dump the entire transaction log. 3. You are running out of space. I will do this so as to prevent the cycling between recovery models on occassion (since that is a prod setting change and may require an rfc for instance). 1. Backup the transaction log to the NUL device [
http://www.brentozar.com/archive/2009/08/backup-log-with-truncate-only-in-sql-server-2008/][1] 2. Shrink transaction log 3. Take a full backup 4. Take a tlog backup to get the db out of pseudo simple recovery Of course, if the data does not need point in time recovery, you can just shift to simple and shrink. [1]:
http://www.brentozar.com/archive/2009/08/backup-log-with-truncate-only-in-sql-server-2008/
Thanks Grant & CirqueDeSQLeil !! We were able to shrink by changing to simple recovery but because the log had occupied all of the disk space during the week, no data was recorded.. That's the data loss we faced. Anyways things are back up now... Thanks again for your comments !
16 People are following this question.
