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 |
|
greenstone90
Starting Member
8 Posts |
 Posted - 2010-06-16 : 17:08:53
|
| Hi,We have noticed a sudden spike in the size of the SQL Server .trn files in the past couple days. One thing that changed a couple days was adding a number of NONCLUSTERED indexes to the database. Might that be related?Thoughts on what might be things to check of why my transaction log is getting so big each hour?Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
greenstone90
Starting Member
8 Posts |
Posted - 2010-06-16 : 23:59:13
|
| Hourly tranaction files were like 500MB before, now they get upto 30GB. Thoughts on how it might have to do with the nonclustered indexes? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 02:35:33
|
| Keys in the indexes are on columns that are changing a lot - so an UPDATE to a row is frequently causing a change to a column(s) in the new index(es), that causes the index entry to be shuffled around the index which in turn causes lots of pages in the index to be modified, and if the column change values are "random" the cumulative changes across lots of rows will cause changes to be widely spread over the index - so lots-and-lots (technical term!) of index pages are being logged.Are the columns changing from NULL to "Something" and then rarely changing after that? If so perhaps use Filtered Indexes to keep the initial NULL values out of the index. |
|
|
|
greenstone90
Starting Member
8 Posts |
Posted - 2010-06-17 : 09:02:42
|
| Thanks for the replys so far...The added indexes are all on columns that are never null and never change (they are indexes on integer ids: these ids are foreign-key refernce other tables). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
greenstone90
Starting Member
8 Posts |
Posted - 2010-06-17 : 23:34:00
|
| Nothing has changed (like replication or mirroring), and I can't think of anything else that has changed... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-18 : 03:38:53
|
| Use a Free Trial on a Log Reader to get an idea of what is in the TLog?longshot: Auto-shrink? |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2010-06-21 : 16:48:06
|
| another long shot - any ETL processes running from the scheduled jobs that you may not be aware of?Terry-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby |
|
|
|
|
|
|
SQL Server 2005 .trn files rapidly growing