Fragmented Indexes Alert on System Tables

jalfano

I am getting notified that master..syscomments has 46% fragmentation but when I run DBCC DBREINDEX('syscomments') it tells me

DBCC DBREINDEX cannot be used on system tables

Should the alert be configured to skip system tables?
(note - This is one of our legacy SQL 2000 environments.)

priyasinha

Hi,

Thanks for your post.

You are correct that DBCC REINDEX or DBCC INDEXDEFRAG are not supported for SQL 2000. I checked the documentation from Microsoft for SQL 2005, 2008 and 2008 R2 also and they list that DBCC REINDEX is not supported for system database. You can check the links here:

http://msdn.microsoft.com/en-us/library ... 05%29.aspx - Lists under Restrictions.

http://msdn.microsoft.com/en-us/library ... 90%29.aspx - Lists under Remarks.

But when I try these on system database it actually works. I dont get any error for SQL 2005 and SQL 2008 R2. Though I do get the same error which you have posted if I try on SQL 2000. So even though it is listed in documentation somehow for SQL 2005 and SQL 2008 R2 it doesn't throw error.

I agree with you that we should definitely put this information in help if not disable the check completely for system database. I suppose that you may still want to know if it is heavily fragmented. Even though you cannot defragment it but may be this information will help you when you migrate or do another install of SQL Server. You might make a decision about the space you want to allocate to a database based on this information. What do you think? I have logged this as SRP-2979.

Thanks,
Priya

jalfano

Priya,

I would think that not alerting on the issue would probably be the way to go since there is nothing actionable that can realistically be done to resolve the matter.

Thank you so much for the information and logging the SRP, you guys are great!

John

priyasinha

Thanks John for confirming.

Regards,
Priya