June 22, 2010 at 1:36 pm
Hello, we have 700 GB database in our environment that has a 95 GB tran log. Literally, 99% of the transaction log is free space.
My question is, is it really necessary to have the tran log at 95 GB while only 1% of it is being used?
Thank so much! 😀
June 22, 2010 at 2:17 pm
The quick answer is "It depends."
Is this database a DW or OLTP?
Is the recovery model Full, Bulk_logged, or Simple?
If not Simple, are you running periodic transaction log backups.
Are you running periodic maintenance, index reorg/rebuilds, etc.
One thing to remember, if it grew to this size, there may be a reason, can you think of anything out of the ordinary that may have occured recently, like a large purge of data, that may have been a one-off process?
June 22, 2010 at 2:35 pm
SSChampion, thank you for your response. The recovery model is Full. The database is OLTP.
Yes, we run tran log backups periodically every 10 minutes. Maintenance done weekly. If my memory services me right, we have not had any large purges of data or anything.
About a month or so ago, the tran log was around 65 GB and again, it had 99% free space. Just recently one of the senior staff decided to increase the tran log file size to 95 GB because errors were encountered during the maintenance jobs; wish I had the errors to present here. My guess is that with the tran log this large, there will not be a need for the log to autogrow.
Thanks!:-D
June 22, 2010 at 3:01 pm
Edit -- duplicate post, issue with my BlackBerry.
June 22, 2010 at 3:14 pm
Lynn, that duplicate is down to none at all now.
Were you going to say if there was a need to increase the log file to 95 GB and you do not have a drive shortage leave it as it is?
---------------------------------------------------------------------
June 22, 2010 at 3:40 pm
Hi, what I'm trying to figure out is if there was really a need to increase the tran log size from 95 GB from 60+ when only 1% of the log was being used.
But, there is definitely not a shortage of free space on the disk where the log resides. Was just thinking if that we could shrink it back down, it would shave off about 10-15 minutes on our nightly backups, save a few GB worth of disk space where the backups reside, and also would reduce the amount of time it takes our sys admins' process of putting these backups to tape.
June 22, 2010 at 3:48 pm
But the size of the backup is not affected by the unused part of the transaction log.
If only 1% is used, only 1% will be backed up.
One thing to be aware of is that when you are restoring the database you need to create the full 95Gb logfile. This might be a problem if you need to restore the backup to a smaller machine for some reason.
June 22, 2010 at 4:00 pm
If they decided with good reasons to increase the log size - I would leave it. Even though that takes space away from the backups. I would have them explain the purposes for increasing it to such a large size. Better planning for maintenance could eliminate the need for such a large tran log file (large due to amount used).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 22, 2010 at 4:04 pm
Stefan_G (6/22/2010)
But the size of the backup is not affected by the unused part of the transaction log.If only 1% is used, only 1% will be backed up.
One thing to be aware of is that when you are restoring the database you need to create the full 95Gb logfile. This might be a problem if you need to restore the backup to a smaller machine for some reason.
Yes that's definitly a big annoyance when you hit that wall... it's not like adding and extra 100 GB is a big deal but it's still a big PITA.
Depending on how you do your reindexing job, 95GB (or 13% of the data size) is really not THAT much. One easy way to test this is is to shrink the log back down to 50-60 GB just before running the reindex job and see what happens... that'll tell you alot
Also the actal error would help us guide you as the what the real issue is, or was.
Also I've only seen a log get full once in production and it was because of a bug with sql 7 RTM that couldn't figure out a good way to do a somewhat complex join. I've never seen it since other than having ridiculously low space available on the tempdb drive.
June 22, 2010 at 4:07 pm
Another quick idea, you can run this DBCC SQLPERF(LOGSPACE).
Log that every few minutes, ideally before the t-log backups, during the day for maybe 5-7 days then see what transpires. That'll give you a history of the used space of the log. That way it's a bit easier to go back and find out what loads it and to what max percentage. Then just leave some free space for surprises and you're good to go.
June 22, 2010 at 8:05 pm
Hi Matt, as a DBA I have encountered this issue a great deal and as stated earlier, it does depand. This is the way that I would approach the issue.
Check the maintenance plans to see if indexes are being rebuilt in TempDB, if not this will probably explain the large size of the trans log.
Shrink the Log file to approx 30GB.
Set the growth increment to about 5 - 10 GB, this is so it doesn't spent all its time growing when performing intensive tlog actions.
Review your log backup only to run when the databases is logging transactions.
You could, if possible, put the database into simple recovery when rebuilding your indexes.
Monitor the growth of your database log file, if it does grow.
I find that there is a monthly cycle to the business processes, ore even quarterly, so when the log file has resized at the end of the month, this is probably where you would want to keep it.
It is a case of knowing what your system is doing and when.
June 23, 2010 at 8:55 am
Thank you all for your informative responses! :w00t: I will perform the suggestions above.
I just checked, and during maintenance, the resorting is NOT done in tempdb. Tempdb also resides on a different disk than the user dbs, there is ample free disk space on this box, so I'm going to suggest to select the "sort results in tempdb" feature.
Thanks again! 😀
June 23, 2010 at 9:24 am
How do you figure this will change anything to the log activity?
Log space used grows because data is put there, sorting in tempdb won't change anything on that matter. Tho it might get things done quicker.
Also you need enterprise version to run online indexing jobs.
June 23, 2010 at 9:41 am
Right, this won't change the log activity but was sort of taking this discussion in a slightly different direction when Grasshopper had mentioned to see if that feature was being used. 🙂
Thank you for your input!;-)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply