.TRN File Far Too Large - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

.TRN File Far Too Large

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-12 : 23:12:54

hi experts,

This is 2005 SP3

I backup the t-log on this database every 15 minutes. The data file size is 35 GB. But recently, the size of each .trn file exploded to an average of 800 MB. Just a few days ago, the average size was 40 MB. I'm sure the amount of activity, transaction changes has remained about the same - so I cannot account for the huge increase in size.

... but today I ran some DBCC commands, just trying to find out what caused the size to increase. I ran these:

DBCC OPENTRAN;
results - the oldest open transaction is less than 1 minute old

DBCC SQLPERF(LogSpace);

Suddenly, within a few minutes, the next trans log backup produced a MUCH smaller .TRN file. I was surprised because I thought these commands were just inquiries, without actually changing anything. And subsequent log backups have normal size .TRN files also. Things appear to be back to normal.

** My question is - could either of the above commands have reset the log or caused the next .TRN file to be smaller? **

Thanks, John

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-13 : 04:36:38

Index rebuilds?

--
Gail Shaw
SQL Server MVP

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-13 : 11:08:06

Thanks for the suggestion, GilaMonster.

I only rebuild indexes on Sundays. And yes that certainly drives up the size of the log while that maint job runs.

Are you suggesting that index rebuilds are occuring at other times that are causing the log file to be utilized more than in the past?

Thanks, John

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-14 : 15:18:20

... to followup, is there any way to find out if lots of index rebuilds are taking place (at times when the Maint job is not running)?

Thanks, John

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-14 : 15:44:44

Check other jobs.

SQL won't randomly decide to rebuild indexes, so someone would have to run the rebuild, someone with high permissions. If it's not that, then there's overly-intensive operations, or maybe long-running transactions or similar. Profiler (or more correctly server-side trace) is your friend here.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-05-15 : 04:06:53

Gail: Would successive TLog backups include any "stuck" transactions? or would they only be included in the TLog backup after they committed?

Seems (if I have understood the O/P correctly) that for "days" all the TLog backups have been 800MB each, and after running DBCC OPENTRAN / DBCC SQLPERF(LogSpace) (or possibly some other external event that occurred around that time) the TLog backups are back down to around 40MB again.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-17 : 21:56:57

Kirsten you are correct. The TLOG did decrease for awhile but the joy did not last very long. Within 3 hours they returned to their bloated size.

800 MB .trn files every 15 minutes for a 35 GB data file with an average of 22 consecutive user connections - no jobs are stuck. No jobs are running more frequently than before.

As I learned from other posts on this forum, SQL Server will not simply "decide" to do index rebuilds. And I only schedule those for Sundays and the job completes successfully after updating statistics.

I have requested that the software vendor investigate this because we applied some updates, then within a day I noticed the bloated TLOG sizes. But you know how it is.... sometimes you have to show them where the problem is.

I'm just trying to rule out any possibility of the database spinning out of control - a SQL Server setting or something.

Thanks, john

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-17 : 22:05:48

GilaMonster, you suggested I run a Profiler Trace. Good idea, so I suppose I should focus on the 'Transactions' section of events? can you recommend any specific events - Rollback ?

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources