Transaction Log file size grows exponentially after maintenance Job ?

Question

Transaction Log file size grows exponentially after maintenance Job ?

YeePee

Mr or Mrs. 500

Points: 561
More actions
September 24, 2010 at 9:11 am

#147094

Here is my Scenario
I do run a SQL Maintenance Job which contains an Index Reorganize task. I know that that Index reorganize This process physically reorganizes the leaf nodes of the index. I do run regular full backup after that. Surprisingly my first transaction log backup file after the Maintenance plan is huge compared to the normal days.
Normally it is around 25 MB where as here it is around 60 GB. The database is around 85 GB of data. I do not understand why the Traction Log is large ,there is no change in data although the data has been reorganized ?.
Thanks for the explanation friends.
Regards
YeePee

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Index reorganise is a fully logged operation (in all recovery models). It will grow the transaction log and result in a large log backup, especially if the indexes are very badly fragmented (as they might be if there are any shrink operations occurring).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 2

That is interesting , so Index Rebuild which drops the existing Index and Recreates the index is not logged ? .If I do a Index rebuild my Log file does not grow exponentially .

regards

YeePee

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

YeePee (9/24/2010)
That is interesting , so Index Rebuild which drops the existing Index and Recreates the index is not logged?

Sure it is. All data modifications are logged, no exceptions.

Index rebuilds are fully logged in full recovery, minimally logged in bulk logged recovery.

Normally people complain the other way around, that rebuilds bloat the log and reorganise doesn't. Sure you're just doing a reorganise? No rebuilds, no shrinks?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 4

I will report back on Monday. I made some changes to the plan and hopefully it solves my issue. Have a great weekend.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

For my large tables, I reorg/rebuild indexes one at a time in a script, with t-log backups inbetween.

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 6

May be that is my problem. I have large table called transactions with 6 million records in it. So how can I run the maintenance plan differently ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

YeePee (9/24/2010)
May be that is my problem. I have large table called transactions with 6 million records in it. So how can I run the maintenance plan differently ?

Generally I don't recommend the use of maint plans on large databases at all. Get a custom index build script (like the one at http://www.sqlfool.com) that only rebuilds what's necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 8

I will try to create a custom script to run the index rebuild .

Here is my result set from the weekend.

Index reorganize -After the reorganization the T Log backup was 65 GB.

Index rebuild –After the Rebuild the First T Log backup was around 21 GB.

So the change is data which is being logged is almost one third for Index rebuild. Any Explanation why it so ?

bharathy.ramalingam SSC Enthusiast Points: 113 More actions · Answer 9

Index rebuild is good if the index fragmentation is high, Index reorganize is suitable if the index fragmentation is very low between 5% and 30%. Please, check the index fragmentation %, it could be high, which results in high log file usage.

Also refer this page: http://technet.microsoft.com/en-us/library/ms189858.aspx

Feeg SSCrazy Points: 2766 More actions · Answer 10

I'm using this index reorganize/rebuild script ==> http://sqlfool.com/2010/04/index-defrag-script-v4-0/

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

I highly recommend the entire (FREE) maintenance suite from ola.hallengren.com, especially the indexing stuff.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service