sql server: reorg / rebuild fragmented indizies (blows up transaction log)

--0) tables with no primary key - create key first to reorg / rebuild indezis

select s.name, o.name, I.Name

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where I.name is null order by s.name,o.name







--1) reogranize indexes

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Reorganize;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc







--2) rebuild indezies where reorg hasn't succeeded

select s.name, o.name, I.Name,st.avg_fragmentation_in_percent,

'alter index '+i.name +' on ' + o.name + ' Rebuild;'

from sys.dm_db_index_physical_stats (DB_ID('CRM'), Null, NULL, NULL, NULL) st

join sys.indexes I on St.object_id = I.object_id AND St.index_id = I.index_id

join sys.objects o on St.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id

where st.avg_fragmentation_in_percent>5 and I.name is not null

order by st.avg_fragmentation_in_percent desc