October 31, 2008 at 12:01 pm
I have just recently taking over responsibility for a sql server at my work. the last guy had a maintenance plan that had wasn't working. The transaction log has grown so large that it has filled the hard disk.
Since there wasn't enough room on the hard drive to do a backup, I instead made a backup onto another hard disk. Now I am wondering the best way to reduce the size of this transaction log. Should I just remove the file? Or can I truncate it?
The transaction log is 230GB.
Thanks for your help.
October 31, 2008 at 12:22 pm
Don't delete the transaction log file!
You can shrink the log file using DBCC SHRINKFILE. If that doesn't shrink sufficiently, then you can use the TRUNCATEONLY option, but that's a last resort (and apparently not available in 2008).
October 31, 2008 at 12:25 pm
Once you've backed up the log, you should be able to use DBCC SHRIINKFILE() on the log file. This will reduce the physical size of the log. How much should you reduce it? Good question. It would be good to know your normal high-water mark on it, but I would guess you don't have that kind of info handy seeing that the last guy wasn't even taking care of the basics. A good rule of thumb would be to make the t-log about 1/2 of the size of your MDF file.
Also, if you use TRUNCATEONLY, keep in mind that that will break the log chain and you will not be able to recover point in time until you run a full/diff backup again.
October 31, 2008 at 12:29 pm
OK, I did a shrink on the log file and it released about 5gb. It's still over 200gb. The data file is only 600mb. Is truncating the only way to get the log to drastically reduce in size? I wouldn't think it would need more than 10gb at the most.
October 31, 2008 at 12:32 pm
Is this a production DB? If so, you'll want to wait for down time to run the BACKUP LOG command with TRUNCATEONLY. Unless, of course, you don't care about point in time recovery. I would say to run the truncateonly, shrinkfile, then get a full backup pronto.
October 31, 2008 at 12:36 pm
This is a production DB. It's a DB that our company RMS uses. I can boot everyone off it whenever if need be.
So truncate, shrink, and backup..
October 31, 2008 at 12:51 pm
And then make sure you have periodic transaction log backups running to keep the transaction log from growing so big again.
October 31, 2008 at 1:04 pm
Check the value of log_reuse_wait_desc in the sys.databases view. That will say why the space in the tran log is not been released. If it's 'Backup log' or similar then you just need to run a log backup (or backup with truncate, full backup). If it's anything else (other than nothing) then there's something else preventing the log space from been reused.
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
October 31, 2008 at 1:49 pm
The latest value in that field (created on the 27th of this month) was "ACTIVE_TRANSACTION"
October 31, 2008 at 2:02 pm
Then you've got an open transaction somewhere. That will prevent log reuse as logs can only be truncated to the beginning of the oldest open transaction.
What does DBCC OPENTRAN return?
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
October 31, 2008 at 2:12 pm
That returned "No active open transactions."
October 31, 2008 at 2:25 pm
Ans sys.databases still shows the same value for the log reuse for that database?
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
October 31, 2008 at 2:41 pm
Now it says "LOG_BACKUP"
October 31, 2008 at 3:52 pm
So I'm reading up on truncating the log file with the SHRINKFILE method.
Is this the way I should use it?
DBCC SHRINKFILE('database_log.ldf',TRUNCATEONLY)
And this will ultimately remove all transactions from the log file? To which I then have to perform a full database backup?
November 1, 2008 at 2:35 am
liam.bell (10/31/2008)
And this will ultimately remove all transactions from the log file? To which I then have to perform a full database backup?
No. Shrink file will just change the size of the file. It won't discard log entries.
After a normal transaction log backup shrink the file down to a reasonable size. The transaction log backup will remove older entries (after backing them up) and then you can shrink the file.
Don't shrink it too small. If it has to grow again it will slow the system down while it grows.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply