growth above normal log database!

Question

growth above normal log database!

rmedeiros

SSC Enthusiast

Points: 124
More actions
December 10, 2014 at 10:28 am

#295824

I have a 800GB database and have done the maintenance reindex dawn that is taking on average four hours, two days I noticed that the log is growing considerably to 140GB while reindexing, but this is causing me not completing the full disk maintenance , someone will have an idea of may be happening.
excuse the English is translated by Google
Ricardo

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 1

rmedeiros (12/10/2014)
I have a 800GB database and have done the maintenance reindex dawn that is taking on average four hours, two days I noticed that the log is growing considerably to 140GB while reindexing, but this is causing me not completing the full disk maintenance , someone will have an idea of may be happening.
excuse the English is translated by Google
Ricardo

REINDEX is fully logged when in the FULL Recovery Model. REORGANIZE is fully logged no matter which Recovery Model you're using.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

Its not surprising if you are rebuilding the clustered index as well which will effectively rebuild the entire table via the TempDB, I'll take a stab in the dark and bet that the size of your largest table is about 100-130GB.

Have you considered doing a Targeted REINDEX, ie only re-indexing when you hit a greater than 30% fragmentation and have more than a couple of 1000 pages on an index?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

rmedeiros SSC Enthusiast Points: 124 More actions · Answer 3

thank you for helping me, which would be Targeted REINDEX do not understand, my biggest table 507307696 KB.

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 4

Jason-299789 (12/11/2014)
Its not surprising if you are rebuilding the clustered index as well which will effectively rebuild the entire table via the TempDB, I'll take a stab in the dark and bet that the size of your largest table is about 100-130GB.
Have you considered doing a Targeted REINDEX, ie only re-indexing when you hit a greater than 30% fragmentation and have more than a couple of 1000 pages on an index?

Just to be clear... only the sorting can be relegated to TempDB. For any index over 128 extents (that's only 8MB folks), the original index will be preserved until the new index is rebuilt in the same file group. So if you have a 500GB clustered index that needs rebuilding, at the end you'll have 500GB in the file group that's empty. REORGANIZE avoids that problem but it's always fully logged and doesn't rebuild the B-TREE or update stats auto-magically.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

I would recommend this for reading:

http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

rmedeiros SSC Enthusiast Points: 124 More actions · Answer 6

Dear changed the log full bank for simple did shrink and it worked,

Thank you all for the help

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 7

rmedeiros (12/11/2014)
Dear changed the log full bank for simple did shrink and it worked,
Thank you all for the help

AND??? You do realize that you need to at least do a DIF backup to re-establish the log file chain, right? And that before you do that, you'll need to put the database back in either the FULL or BULK LOGGED Recovery Model, right?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)