Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2010-03-17 : 20:04:25
|
| Hello, this is SQL server 2005.The maint plan reindexes tables and views and sets free space to 5 percent, updates statistics. It has ran flawlessly for 2 yearsMy database is 8 GB. recently the weekly maint Plan has been causing the log to become full - the max. log size is also 8 GB It fills the log even though I shrunk the log just before running the maint plan. this has happened 2 weeks in a row.I recently re-ran the maint plan again after disabling the TLOG Backup job. The results were the same. The tlog still became full while the plan was executing. I had to terminate the maint plan, then shrink the log file. (It is worth noting that I am running the maint plan while the system is in use, but it is a time of less use than normal.)Now, many indexes are heavily fragmented and more tables scans are occuring than were weeks ago when the maint plan was running correctly.2 questions:1. What could be causing the maint plan to fill the log - an 8 GB log for a datafile size of 8 GB seems way too large ?2. Will I have to get all users out for a few hours, then run the maint plan to get my tables back in good working order - is that my best option at this point?I welcome all ideas. Thanks, John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-17 : 20:47:17
|
| 1. Do NOT shrink the log! There is absolutely no reason for this since it's going to grow again as the database needs that size.2. Definitely do not disable the tlog backup job during index rebuilds. You need that running, perhaps on a more frequent basis.3. An 8GB tlog file size does not seem too big for an 8GB data file size. It just depends on how often you are backing up your tlog and how big the indexes are to be rebuilt.4. You do not need to kick anyone out to get anything in order. It won't help any.5. Do not set a maximum file size for the tlog. Let it grow to what it needs to be in order to avoid the tlog full error. Instead, backup your tlog more often to control the size.How often are you backing up your tlog? We do ours every 15 minutes to help control the tlog size as well as give more recovery points.Why do you keep shrinking the tlog? You are creating a huge performance problem by doing this as you are not only heavily fragmenting the file, but you are getting a huge performance penalty when it has to expand again. Are you so tight on disk space that an 8GB file is a concern? If so, then you need to add disk space for the health of your database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-03-17 : 21:00:30
|
| Thanks Tara.I backup the log every 2 hours and the trn file is usually under 100 MB.I asked for your opinion and I value it, but I don't agree that is is normal for the log to grow from 1 GB to 8 GB during the 60 minutes the maint plan runs. I did run DBCC CHECKDB on this db and it comes up with 0 inconsistencies.You said "You do not need to kick anyone out to get anything in order. It won't help any."Please enlighten me about this. I thought that heavily used tables, indexes would be bypassed by the Maint Plan (i.e. not reindexed) if an exclusive lock could not be obtained. Am I wrong about that? thanks, John |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-03-17 : 23:31:02
|
| ... and the Plan uses the REINDEX task and adjusts free space in each table to 5 % |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-18 : 00:13:24
|
| DBCC CHECKDB is not relevant to this issue. That checks for data corruption. What you are encountering are logged transactions not getting backed up. You need to backup your tlog more often, such as every 15 minutes. At the very least, switch to hourly or every 30 minutes. You won't encounter a performance issue by increasing the frequency of them. The benefit is you get to control the size of the tlog better plus you get more recovery points in the case of a failure.Index rebuilds do not take exclusive locks on tables. If you are using Enterprise Edition, then the index rebuilds in most cases can be done ONLINE. If you aren't using that edition, then the index rebuilds are done OFFLINE. But that just means the index is offline and nothing else. That particular index just isn't available to queries until the rebuild completes. Some people choose to REORGANIZE instead of REBUILD if they can't do it ONLINE. You won't have that versatility of switching with a maintenance plan, so you'd need to switch to custom code such as the one I wrote: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspxI don't recommend using 5% as the fill factor, neither does Microsoft according to Books Online. The default is 0/100, which is what Microsoft recommends. We did extensive analysis with switching the fill factors, and the best performance was when it was at 0. You should test it out on your own in a performance environment, but this is stated in Books Online.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-18 : 03:51:19
|
Backup the TLog every 15 minutes. That will make the space used by the completed transactions available for reuse (by new transactions). Otherwise when the Tlog fills up it has to be extended to store the extra transactions whilst waiting for the next backup to occur.You will backup exactly the same number / size of transactions whether you backup the TLog once a day, or every minute, so no reason not to do it frequently.However, there is an overhead on each file created - so doing it, say, every minute is counter productive, and also to restore you will have many more TLog files to restore, so the balance is at around 10-15 minutes.Upside is that if you have a disaster you will have a backup from at the most 15 minutes ago, rather than 2 hours as at present.We choose the fill-factor on an index-by-index basis. For example, any index based on an IDENTITY column should be set to 100% fill - new numbers will only be added at the end, so there is no point leaving gaps in the middle of the index  For random indexes we use 5% free space if the tables are small, or there is a huge number of inserts in the index, but other than that we set to 100%; if 99% of the pages in the index will have NO inserts before the next Index Rebuild then there is no point leaving space for them, just let the additional inserts split the indexes pages where they do need to be added (particularly if new inserts are "grouped" tightly and there are lots of inserts to specific index pages - they are going to cause a page split anyway  )" I backup the log every 2 hours and the trn file is usually under 100 MB."That suggests that the transactions created by "users" is 50 MB / hour. Modest If your maintenance plan takes less than 2 hours and, lets say, starts just after a TLog backup then the whole 100% of the TLogs it generates will be stored in the TLog (LDF) file - until the next TLog backup which will mark the space for reuse. That can easily be a similar total size to the database itself - every index is moved to the end of the MDF file so that it is "contiguous", its one heck of a lot of shuffling data around, and thus lots of TLog entries - after all, if there was a power cut you would expect SQL to be able to sort it all out when the power came back on Increasing the frequency of the backups will help with this, but not entirely. Some of the transactions from Index Rebuild are huge, and Backup won't release a transaction until it has finished and been committed.You may be able to decrease the frequency of Index Rebuilds. If you are using the Wizard it will be just "doing the whole lot" I expect. To get beyond that you will need to engineer a solution, as Tara has suggested. Rebuilding indexes on only tables where the indexes are fragmented will help a lot (reduce TLog size, reduce backup size, reduce disk space required, take less CPU time), but the engineering is not trivial - there is a risk that you don't fully understand the new solution you put in place and it causes some problem that you had not anticipated - like jumping out of the frying pan into the fire!An alternative might be to rebuild tables A-M on Monday, and tables M-Z on Tuesday etc. so that the load is reduced each night. Its a very crude approach, but is simple Its a pity that the Wizard does such a crude job, as it is the tool that I expect most people rely on |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-18 : 11:30:13
|
quote:
For random indexes we use 5% free space if the tables are small, or there is a huge number of inserts in the index, but other than that we set to 100%; if 99% of the pages in the index will have NO inserts before the next Index Rebuild then there is no point leaving space for them, just let the additional inserts split the indexes pages where they do need to be added (particularly if new inserts are "grouped" tightly and there are lots of inserts to specific index pages - they are going to cause a page split anyway )
We did extensive analysis on SQL Server 2005 and found that SELECT queries suffered dramatically from anything other than 0%. We were looking to improve the writes by adding free space for the random ones, however our reads were more important it turned out.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2010-03-18 : 11:55:50
|
| Thanks very much for the advice Kristen and Tara! I'm switching the log backup interval to 15 minutes today.John Memphis TN USA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-18 : 12:12:35
|
quote:
Originally posted by tkizerWe did extensive analysis on SQL Server 2005 and found that SELECT queries suffered dramatically from anything other than 0%. We were looking to improve the writes by adding free space for the random ones, however our reads were more important it turned out.
Good point. Reads are 10x as popular as Writes around here too! |
|
|
|
|
|
|
|
|
Maint Plan Caused TLOG to Fill