| Author |
Topic |
|
NifflerX
Starting Member
29 Posts |
 Posted - 2011-09-16 : 10:47:42
|
| Hello,I've seen tons of threads on here about transaction logs not shrinking, but I still can't figure out what's going on with my database. I have .mdf file that is 11.7GB and a .ldf file that is 27.6 GB. The database is set to full recovery model and I take hourly transaction log backups that are usually quote small, often under 1 MB. In addition I do nightly full backups. The only reason the database gets large at all is a large influx of data in the morning, on the scale of 7 GB. So, I would expect the transaction log to get big in the morning, but by the afternoon I would expect it to have shrunk back down, given the hourly backups. But that's not what happens, the log file keeps growing and now it's huge. Does anyone have any ideas that I could try to shrink the transaction log, and more importantly keep it from growing to this size? Thank you.-NifflerX |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-09-16 : 10:58:13
|
| Thank you very much for the reply.How come my system needs such a large transaction file? After the morning load the database has very few transactions that are not SELECT statements. I figured that because the system only really loads data in the morning the transaction log would not need to be nearly that big. 27GB is approaching more than my system can handle, and if the log isn't shrunk when I do backups how will increasing the frequency of the backups help manage the size of the .ldf file? Thank you again for all your help.-NifflerX |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-09-16 : 11:22:48
|
| Thank you again for the reply. I ran the profiler just to get a sense of what's going on, and while I only ran it for a short time, I only see a bunch of SELECTs, not any updates or inserts. Would those selects also increase the log file's size? Thank you again.-NifflerX |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-16 : 13:56:00
|
| No. Selects aren't logged.The log size is likely due to index rebuilds. The log has to be big enough to accommodate the largest transactions that you have. The 7GB data import can easily require 16 or more GB of log space.--Gail ShawSQL Server MVP |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-09-16 : 15:27:51
|
| Thank you, thank makes sense to me. For the 16 GB worth of indexing, is that kept in the .ldf file after the indexes are rebuilt, or is the .ldf only keeping the record of their creation? I'm was hoping that if it's the second one, then the .ldf could be shrunk after the indexes are rebuilt in the morning. That would mean that the .ldf file would increase a lot during the index rebuilds but after would be able to shrink back down. Is something like that possible, and recommended? If so, are there any sites or instructions I could look at to get ideas on how to get to that state. Anything to decrease the amount of space my .ldf files are taking up. Thank you again.-NifflerX |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-16 : 17:21:40
|
| DO NOT regularly shrink your log file. Growing the log is an expensive operation, growing causes log fragmentation if your auto-grow is set badly and shrinking gains you absolutely nothing except a temporary increase in free disk space (temporary cause it will drop down again when the log grows)--Gail ShawSQL Server MVP |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-09-16 : 17:47:04
|
| Thank you again for all the replies. If I don't shrink the log file how can I regain some of the space. At this point the log file is nearly 2.5 times bigger than the mdf file and if that ratio continues I'll run out of space on my server much faster than originally anticipated. Is there really no way for me to contain the growth of my log file? Are there things I could try to lessen the transaction written when I load the data in the morning? I thought that once a full backup was done the log could be truncated because all transactions had been committed and so the .bak file contained a complete, committed, backup of the database. Given that line of thinking, I didn't expect the log file to be nearly as big as it has grown to. Thanks again so much for all the help.-NifflerX |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-09-16 : 18:08:14
|
| That is not true about full backups. You never truncate the tlog when using full or even bulk_logged recovery models. You run frequent tlog backups though. Only the tlog backups will truncate it (the completed portion). Tell us about your index rebuilds. It probably isn't going to keep growing. A log 2.5 times bigger than the MDF is not a concern since you have a large import occurring and index rebuilds. How big is your biggest index? And have you figured out just how much log is needed for your import?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-16 : 19:57:10
|
quote:
Originally posted by NifflerX
Thank you again for all the replies. If I don't shrink the log file how can I regain some of the space.
Why do you need to? What are you going to do with that space other than leave it as 'free disk space' that is of no use to anyone?quote:
At this point the log file is nearly 2.5 times bigger than the mdf file and if that ratio continues I'll run out of space on my server much faster than originally anticipated. Is there really no way for me to contain the growth of my log file?
Providing you're taking regular log backups, it won't continue to grow. It'll grow until it reaches the size it needs to be for your database activity and the frequency of the log backups and once at that size it will stay at that size.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-18 : 04:07:32
|
| "How come my system needs such a large transaction file?"The Log file size stores transactions. When you back up the transaction log the space is reused (but the file is not shrunk). It does not shrink because that continuous Grow/Shrink would cause fragmentation which would then make the system perform really badly.Look at your log backups to see how big they are. Are they exceptionally big at a particular time of the day (or night), or a particular day of the week?The usual culprit is housekeeping rebuilds of Index files and freshening of Statistics. We increase the frequency of TLog backups during index rebuilds to every 2 minutes to prevent abnormal expansion of our LOG files.If you, once, had an incident which cause huge transaction log file growth you could shrink the log back down to its "normal" size, don;t do it for any other reason through, and not until you have fixed the underlying problem.Suggest you go look at recent backup sizes to see if there is a pattern to the "big ones". |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-09-19 : 09:49:41
|
| Thank you very much for the replies.We haven't had much in terms of exceptionally large loads, just the standard ones daily. However, over the weekends we run more intensive maintenance jobs, like the Shrink Database, Rebuild Index and Update Statistics right in a row (and all defined through the Maintenance Plan GUI) and this morning the log file is down to 19GB. Not exactly small, but much smaller than before. I'll watch the log file all week to see if it's going up or down from this point, and while this still seems large for the number of transactions the database is receiving, it may be what it is.Also, in reply to what else I would do with the space if I could get it back. I would load more database on the server. The memory usage is fine and the CPU is actually under utilized, so from a performance stand point I think putting more databases on the server is a definite possibility, but obviously not if there's no space.Thank you again for the replies, and if anyone has any other ideas on what sort of things I should be especially looking for to determine if the log file is the right size, please let me know.-NifflerX |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-19 : 10:06:57
|
| "we run more intensive maintenance jobs, like the Shrink Database, Rebuild Index and Update Statistics right in a row"I hope you haven't actually got Shrink Database scheduled as a regular task?Did you review the size of Tlog backup files to see which ones are exceptionally larger than "normal"? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-19 : 10:07:42
|
| One thing that you can do immediately. Stop Shrinking your Database! You're causing all sorts of harm. If the index rebuild is before that you're causing massive fragmentation for no good reason. If the rebuild is after, you're re-growing the DB and possibly causing file-level fragmentationShrinks of any form (data file, log file or database) should be done under exceptional circumstances, never regularly scheduled.--Gail ShawSQL Server MVP |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-09-21 : 13:14:46
|
| We do have shrink database jobs scheduled as regular tasks. We don't have an in house DBA, I do my best, but we had some consultants come in to help us setup our databases and more importantly our maintenance plans. They recommended, and implemented the hourly transaction log backups as well as the nightly full backups for all the databases, including system databases. In addition, they setup the shrinks to attempt to keep the size of the files from growing to large. On that point, the ldf file as grown to 20 GB back up from 19GB after the shrink. As to the tlog backup files, none seemed larger than normal. We had the same large ones in the morning, and then as the day went on very small ones as no new data was inputted into the system. I've reached out to the consultants to ask them why they put in the shrink jobs, since everyone here is saying that shrinking should not be used on a ongoing basis.Kristen, one of the things you mentioned was increasing Tlog backups during the index rebuilds. Won't doing this simply create more backups and thus take up more file space? Thank you again.-NifflerX |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-21 : 13:28:51
|
| I do hope there are index rebuilds after the shrinks, otherwise those shrinks are going to be playing havoc with your performance due to index fragmentation.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-21 : 13:30:44
|
quote:
Originally posted by tkizer
Adding shrink jobs or using autoshrink is a sign of inexperience with database administration. I would question your consultants experience due to this.
Absolutely. In fact, a consultant who schedules database shrink jobs is one that I do not want anywhere near my database ever.--Gail ShawSQL Server MVP |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-09-21 : 13:51:05
|
| There are index rebuilds as the 2nd to last step in all the maintenance plans, the last step being cleanup (removing old .bak, .trn and report files). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
Transaction log not shrinking