Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Questions on Index Rebuilding (not reorganize) Expand / Collapse
Author
Message
Posted Wednesday, June 5, 2013 1:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:40 AM
Points: 1,380, Visits: 2,699
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
Post #1460055
Posted Wednesday, June 5, 2013 2:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 2,404, Visits: 2,941
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’! **
Post #1460071
Posted Wednesday, June 5, 2013 9:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 26, 2013 5:58 AM
Points: 1, Visits: 7
Hi,


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


Regards
Dileep
Post #1460512
Posted Wednesday, June 5, 2013 10:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:13 PM
Points: 31,214, Visits: 15,660
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
Post #1460516
Posted Sunday, June 9, 2013 6:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 12:29 AM
Points: 93, Visits: 407
thank u all for ur kind help!
Post #1461364
Posted Thursday, June 13, 2013 11:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 12:29 AM
Points: 93, Visits: 407
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
Post #1463390
Posted Thursday, June 13, 2013 11:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1463391
Posted Thursday, June 13, 2013 11:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 12:29 AM
Points: 93, Visits: 407
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!
Post #1463393
Posted Thursday, June 13, 2013 11:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1463394
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse