June 16, 2004 at 2:16 pm
I am trying to script DBCC SHOWCONTIG, using various online scripts I have perused.
However, I have noticed an odd thing. On my server (SQLServer 2000), I have some tables with no clustered index. For those tables, when I run "DBCC SHOWCONTIG", rather than giving me results, it gives me none.
For example, it will say: "DBCC execution completed. If DBCC printed error messages, contact your system administrator."
Example commands:
a. DBCC SHOWCONTIG ('TRANSACTIONS')
b. DBCC SHOWCONTIG ('TRANSACTIONS') with fast
c. DBCC SHOWCONTIG ('TRANSACTIONS','IX_Transactions')
The same DBCC SHOWCONTIG command executed against a similar schema on another server, is giving me full results.
1. Are there any known reasons why DBCC SHOWCONTIG might return empty results?
2. Are there any known bugs with DBCC SHOWCONTIG?
(The only thing I can think of is that I've restored the database--some time ago from another server. Maybe there is some issue with the restore? I did verify through SQL Enterprise Manager that this table has 8 nonclustered indices and no clustered indices.)
Mark
June 17, 2004 at 6:12 am
Did you try
DBCC SHOWCONTIG ('TRANSACTIONS') WITH ALL_INDEXES
?
June 17, 2004 at 6:22 am
Yes: DBCC SHOWCONTIG ('TRANSACTIONS') with all_indexes did not return any results.
No variant of it returned results for this table (and some other tables in my database). The only noticeable similarity among these tables is they were not clustered. (But in my other database, these same non-clustered tables, respond to DBCC SHOWCONTIG.)
This is a very serious issue for defragmentation, because most scripts I've seen on the internet rely on DBCC SHOWCONTIG to identify indices in need of fragmentation. If there are some tables which do not generate results, they may escape review for defragmentation.
June 17, 2004 at 6:36 am
Have you done a dbcc checktable?
I'd also attempt bcping the data out, creating the indexes and then running a showcontig on that, check if you get results, if so drop the original and rename the new.
June 17, 2004 at 6:43 am
>>Have you done a dbcc checktable?
It passed. And I discovered this instance of the table had 0 rows.
I inserted one row and DBCC SHOWCONTIG worked.
Mystery solved: 0 row tables do not get any results from DBCC SHOWCONTIG.
(I think this is not documented by MSFT. It is helpful for correlating DBCC SHOWCONTIG results with indexes.)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply