SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Questions on Index Rebuilding (not reorganize)


Questions on Index Rebuilding (not reorganize)

Author
Message
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4748 Visits: 2969
It's worth noting that ALTER INDEX........WITH (ONLINE = ON) while it's not a magic button, it is an excellent option though.

When rebuilding indexes online, short lived, shared locks are still taken at the beginning and end of the rebuild process.


Regards
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8316 Visits: 3718
chewychewy (6/4/2013)
Thanks. In the url it stated that when rebuilding offline, select statement still can be performed on the table.

As the url stated, this only applies to NON-clustered indexes. When rebuilding a CLUSTERED index offline, a select on the table will be blocked.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
dileep1911
dileep1911
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
Hi,


Could you please send me the scripts to how to find fragmentation and removing.


Regards
Dileep
Steve Jones
Steve Jones
SSC Guru
SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)

Group: Administrators
Points: 141312 Visits: 19416
We have lots of scripts to find fragmentation:

http://www.sqlservercentral.com/search/?q=fragmentation&t=s

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
chewychewy
chewychewy
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 520
thank u all for ur kind help!
chewychewy
chewychewy
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 520
Hi All experts,
One last question.
Let's say there is a need to rebuild 50+ indexes (due to fragmentation).

I did a check on the DB and the sum of all the indexes to be rebuild is 150GB.
The largest index is about 20 GB size.
Let's say my db recovery model is full and i doesn't do any transaction log backup during that time.
Understand that the target database need free space of the largest size index for rebuild.
But Does it really need 150 GB of transaction log size to do the rebuild?
thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214874 Visits: 46269
chewychewy (6/13/2013)
But Does it really need 150 GB of transaction log size to do the rebuild?


Probably more than 150. 200 or 250 to be safe.

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


chewychewy
chewychewy
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 520
appreciate ur help!
recently just for test purpose, i actually rebuild a db woth of 100 GB indexes.
But only used up 16GB transaction log.
guess i'l' do some additional testing.

thanks!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214874 Visits: 46269
chewychewy (6/13/2013)
recently just for test purpose, i actually rebuild a db woth of 100 GB indexes.
But only used up 16GB transaction log.


The database was in simple or bulk-logged recovery model.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search