June 17, 2010 at 7:13 am
Hi,
We have noticed a sudden spike in the size of the SQL Server .trn files in the past couple days. trn files used to be ardound 300kb, but now get as big as 30gb (have the transaction files backed up hourly).
One thing that changed a couple days was adding a number of NONCLUSTERED indexes to the database. All of these indexes were on id columns that are foreign key referenced to the primary key columns of other tables: Also, the value of these key fields never changes. Might that be related?
Thoughts on what might be things to check of why my transaction log is getting so big each hour?
Thanks!
June 18, 2010 at 7:03 am
It's possible that if you're running REINDEX or other defrag routines that the new indexes could be affecting the log size. Other than that, no, I can't think of a reason why you'd see bigger logs because of added indexes. Usually it breaks down to being uncommitted transactions, large transactions, lots of transactions or if you have replication turned on, you're not replicating the transactions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply