June 8, 2012 at 1:37 am
Hi All,
I am in need of a script which can take out the names of the indexes which will fail during the online rebuilding. So that I will disable them and continue with my ALTER INDEX REBUILD ALL syntax.
It is known that usually the LOB(Large objects) data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml fail while rebuiling online, so I need script to identify them and exclude these indexes while rebuilding. However, I can later reorganise the LOB indexes online.
Please help.
June 8, 2012 at 1:55 am
Not got 2005 installed any more so not tested it on 2005, but works on 2008R2, but this should give you the basics, might need a few tweaks.
SELECT
IndexName = i.Name,
ObjectName = o.Name,
ColumnName = c.name,
ColumnType = t.name,
ColumnLength = c.max_length
FROM
sys.indexes i
INNER JOIN
sys.objects o
ON
i.object_id = o.object_id
INNER JOIN
sys.index_columns ic
ON
i.object_id = ic.object_id
AND
i.index_id = ic.index_id
INNER JOIN
sys.columns c
ON
ic.column_id = c.column_id
AND
ic.object_id = c.object_id
INNER JOIN
sys.types t
on
c.system_type_id = t.system_type_id
WHERE
o.type = 'U'
AND
t.name in ('image','text','ntext')
UNION
SELECT
IndexName = i.Name,
ObjectName = o.Name,
ColumnName = c.name,
ColumnType = t.name,
ColumnLength = c.max_length
FROM
sys.indexes i
INNER JOIN
sys.objects o
ON
i.object_id = o.object_id
INNER JOIN
sys.index_columns ic
ON
i.object_id = ic.object_id
AND
i.index_id = ic.index_id
INNER JOIN
sys.columns c
ON
ic.column_id = c.column_id
AND
ic.object_id = c.object_id
INNER JOIN
sys.types t
on
c.system_type_id = t.system_type_id
WHERE
o.type = 'U'
AND
t.name in ('varbinary','varchar','nvarchar','xml')
AND
c.max_length = -1
June 8, 2012 at 3:29 am
Super script..you are a life saver anthoney 🙂
June 8, 2012 at 3:44 am
Not a problem, happy to help as always
June 8, 2012 at 3:50 am
If I may recommend... Don't reinvent the wheel, don't rebuild everything without checking. Use an existing index rebuild script
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
http://ola.hallengren.com/Versions.html
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply