Wednesday, March 7, 2012

DBCC DBREINDEX not working

I have a stored proc that identifies indexes that need to be
defragmented ( LogicalFragmentation > 20% ) using DBCC SHOWCONTIG.
This works fine and shows me all of the candidate indexes to be
defragged. I am using DBCC DBREINDEX to rebuild the indexes. What I
noticed yesterday is that it seems that DBREINDEX is not actually doing
anything. I would check the target indexes, run DBREINDEX, then check
again. The same list of indexes came up unchanged.

What is strange is although I am not specifying "WITH NO_INFOMSGS" this
is the only output from the DBREINDEX:
"DBCC execution completed. If DBCC printed error messages, contact your
system administrator."

According to BOL DBREINDEX should return a result set if "NO_INFOMSGS"
is not specified, but if "NO_INFOMSGS" is supplied the prior message is
what is returned. This doesn't seem to be working correctly, or if it
is, I have no idea where any error messages are being returned.

Anyone have any ideas?In this case, the documentation from Books Online is incorrect. The
documentation has been corrected in SQL Server 2005, where it says:

Result Sets

Whether any one of the options, except NO_INFOMSGS, is specified
(the table name must be specified), DBCC DBREINDEX returns:

DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

Razvan|||Nice documentation.

Thanks for the heads-up, Razvan.

No comments:

Post a Comment