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

Index question Expand / Collapse
Author
Message
Posted Friday, November 22, 2013 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:17 PM
Points: 12, Visits: 155
Is there a way to script out the ONLINE option for an index? I want to list out the indexes and whether they were created with ON or OFF.

I can find pretty much everything else about the index except that.

Thank you,
Josh
Post #1516863
Posted Friday, November 22, 2013 10:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 12,744, Visits: 31,067
whether the command was run with ONLINE or not is not saved anywhere, I'm pretty sure.


peek at this scripting example, it essentially the same way i do it:

http://www.sqlservercentral.com/scripts/Indexing/63620/


generate the script with ONLINE if the server version is Enterprise or Developer, and the table does not contain any columns of datetype ('image','text','ntext','xml')


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1516870
Posted Friday, November 22, 2013 10:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:17 PM
Points: 12, Visits: 155
Thanks Lowell!
Post #1516876
Posted Friday, November 22, 2013 1:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:39 AM
Points: 2,728, Visits: 2,640
You have an option "Keep index online while reindexing" in the Rebuild Index Task element in SSIS. Pressing button "View T-SQL" will script the indexes with the ONLINE=ON.
You can also play with the FillFactor and Sort in Tempdb settings.

Regards,
IgorMi
Post #1516914
Posted Saturday, November 23, 2013 2:22 AM


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 @ 11:47 AM
Points: 41,525, Visits: 34,442
Lowell (11/22/2013)
whether the command was run with ONLINE or not is not saved anywhere, I'm pretty sure.


It's not, because the option only affects the process of rebuilding and has no effect whatsoever on the index once created/rebuilt.



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 #1516997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse