Index maintenance increase log size

Question

Index maintenance increase log size

Viewing 10 posts - 16 through 24 (of 24 total)

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

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

Take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

In that case, I'm not sure why your transaction log should grow to nearly three times the total size of your indexes. Does that 60GB include the clustered indexes? Do you have any indexed views?

John

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 3

There are no indexed view. Yes, clustered indexes are included in that 60GB.

John Mitchell-245523 (4/29/2013)
In that case, I'm not sure why your transaction log should grow to nearly three times the total size of your indexes. Does that 60GB include the clustered indexes? Do you have any indexed views?
John

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 4

One question to all:

If I have data like this, where there is only one clustered index on table and index size is 2976 KB.

name rows reserved data index_sizeunused

ABCTablee13483489 1437656 KB 1429896 KB2976 KB4784 KB

Rebuilding this index will use 1429896 KB of data or 2976 KB of data?

thanks

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

John Mitchell-245523

SSC Guru

Points: 148809

April 29, 2013 at 8:31 am

#1610838

Don't know. Where did that data come from?

John

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 6

This is actual data of one of my table with a clustered index. It comes when I do : sp_spaceused TableName

John Mitchell-245523 (4/29/2013)
Don't know. Where did that data come from?
John

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Well, the clustered index is the data, so I think you're looking at the larger number. Index_size only includes, I believe, the non-clustered indexes (as well as XML, spatial and full-text if you have them). I'm not sure why it's usual for a table without clustered indexes to show a non-xero value in this column.

John

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

S_Kumar_S (4/29/2013)
One question to all:
If I have data like this, where there is only one clustered index on table and index size is 2976 KB.
name rows reserved data index_sizeunused
ABCTablee13483489 1437656 KB 1429896 KB2976 KB4784 KB
Rebuilding this index will use 1429896 KB of data or 2976 KB of data?
thanks

Is there only the clustered index on this table or are there also nonclustered indexes on the table as well? I'm asking because of your wording above "where there is only one clustered index on table" since there can only be one clustered index on a table.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 9

There is only one clustered index on table, no NONClustered indexes are there.

Lynn Pettis (4/29/2013)
S_Kumar_S (4/29/2013)
One question to all:
If I have data like this, where there is only one clustered index on table and index size is 2976 KB.
name rows reserved data index_sizeunused
ABCTablee13483489 1437656 KB 1429896 KB2976 KB4784 KB
Rebuilding this index will use 1429896 KB of data or 2976 KB of data?
thanks
Is there only the clustered index on this table or are there also nonclustered indexes on the table as well? I'm asking because of your wording above "where there is only one clustered index on table" since there can only be one clustered index on a table.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 10

S_Kumar_S (4/29/2013)
There is only one clustered index on table, no NONClustered indexes are there.
Lynn Pettis (4/29/2013)
S_Kumar_S (4/29/2013)
One question to all:
If I have data like this, where there is only one clustered index on table and index size is 2976 KB.
name rows reserved data index_sizeunused
ABCTablee13483489 1437656 KB 1429896 KB2976 KB4784 KB
Rebuilding this index will use 1429896 KB of data or 2976 KB of data?
thanks
Is there only the clustered index on this table or are there also nonclustered indexes on the table as well? I'm asking because of your wording above "where there is only one clustered index on table" since there can only be one clustered index on a table.

If the index rebuild/reorg has to move a lot of pages, the log file usage could be quite a bit more than the index size which, IIRC, is only the size of the B-TREE for clustered indexes. Also if you're doing a rebuild, there will be some statistics that get updated. They all take some space. And, if you have some blobs in there, they're going to get rebuilt unless you tell them not to. That will take some space, as well.

You never did say. What is the total size of your database?

--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)