February 25, 2015 at 12:13 pm
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
February 25, 2015 at 2:43 pm
Jpotucek (2/25/2015)
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
Shrink it. But be careful. I am not saying that you should do this regularly or as part of your weekly DBA activities.
Once shrunk, check the VLFs and adjust it accordingly. Increase or decrease the Tlog file size depending of your database workload. And of course, enable the Tlog backup. Be sure that the frequency of your Tlog backups is also adequate to your workload.
February 25, 2015 at 8:27 pm
I think the question is how to shrink it?
Assuming that you don't need point in time for however long it took to grow to 200GB you can clear the log by switching recovery model to simple and then back to full or with a backup to NUL.
Then take a new Full backup to start the recovery chain because either method kills your ability to recover to an earlier point in time.
I really recommend you decide whether you need point of time on this database or not. If you don't then leave it in simple and the log is much easier to manage. If you do make sure you get a log backup maintenance plan in place.
February 26, 2015 at 1:47 am
If it's never had a transaction log backup, then it's probably safe to assume no one really cares about point in time recovery.
Set the DB to simple recovery model, shrink the log and then have a discussion with the relevant business owners about what data loss allowance they can tolerate on that DB (don't accept 0 as an answer). That will then be a guide as to whether the DB stays in simple recovery or goes back to full and gets log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2015 at 2:20 am
Thank you. I switched to simple recovery mode and was then able to shrink the log file!
February 26, 2015 at 4:26 am
And go and have the discussion with the business people about the recovery point objectives and recovery time objectives for this database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2015 at 5:43 am
Grant Fritchey (2/26/2015)
And go and have the discussion with the business people about the recovery point objectives and recovery time objectives for this database.
+1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply