Indexes going to 99.99% fragmentation nightly

I have a SQLServer 2005 database withseveral tables, each containing between 10 and 40 million records. Eachtable has a clustered index (which clusters data into blocks of about100,000 records or so based on customer), as well as 2 or 3non-clustered indexes (primary key, order codes). I have a maintenanceplan that runs nightly for each table:

DBCC DBREINDEX ('Table') WITH NO_INFOMSGS;

These plans run with no errors, and if I check at night, the indexes all seem to have .01% fragmentation, or something similar.

Yet, by 8am the next morning, all of the non-clustered indexes are 97to 99.999% fragmented. The odd thing is, no users have been on thesystem. No data has been added/deleted/modified.

Has anyone else experienced such behavior? If so, were there any solutions?

Thanks!

<p>&nbsp;I use this to check fragmentation:</p><p>&nbsp;DBCC SHOWCONTIG ('TableName') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS </p><p>&nbsp;The next to last column (LogicalFragmentation) is what I look at (these instructions coming to me from my dba)</p><p>Today, here is an example of one table:</p><p><img src="http://bit.ly/mvc56" mce_src="http://bit.ly/mvc56" height="317" width="750"></p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p>

Oh, and it also seems to happen in a random fashion. Some days this happens on one table, another day on another table. Sometimes this happens on two or three tables at the same time. Sometimes, all tables are OK when I come in in the morning. This has been going on for about 6 or 7 months now. I never see fragmentation occur on the clustered index (in the pic above, the clustered index was not rebuilt the previous night, but the non-clustered indexes were, with success).

Is there an OS level defrag running at night? Not sure of the effect but came to mind. Also possibly try updating the service pack. Can you reproduce this on a test instance?

Going to start OS level defrags tonight. Another person suggested that, too. My dba assures me that an OS disk defrag will not have any effect, since that's what SQL Server does with the clustered indexes. I think he's full of hot air.
Test server? Are you kidding me? That would mean my boss would actually care enough to spend on some extra hardware for his flagship product!

Is there any SQL Server Jobs / OS level scheduled tasks running during the night?

The only thing scheduled at night is the defragmenting of indexes (DBCC DBREINDEX ('Table') WITH NO_INFOMSGS, and then a backup of the database. We used to shrink the database after the index defrag and before the backup... but I read that might not be a good thing. So I have stopped that about 3 weeks ago. Seems to have made no difference at all.
Fill factors are either 80 or 100 on all indexes. We've experimented with different values over the past 3 years, but have never found any real difference as far as day to day performance or the problem in question.
I'm wondering if any sort of corruption could be causing this... and, if so, how do I discover and correct it?

Do you have any text /varbinary(max) column in this table? Can you run the following query and post the resultSELECT
index_type_desc, page_count,avg_fragmentation_in_percent,avg_page_space_used_in_percent
,avg_record_size_in_bytes
,forwarded_record_count,fragment_countFROM sys.dm_db_index_physical_stats(db_id('databaseName'),
object_id(''TableName),default, default, 'detailed');Madhu

<p>&nbsp;</p><p>One of the tables in question has 6 nvarchar(max) columns (out of 58 total columns). Yeah, I know... it's a beast.&nbsp; I didn't design it.<br></p><p>Another table, however, has no (max) columns.&nbsp; This other table only has 8 columns (5 GUIDs, 2 floats, and a bit).&nbsp; Tthere are currently 41,993,652 records.&nbsp; This morning, the non-clustered indexes were (again) all 99% fragmented.&nbsp; I ran the statement above on it, and received:</p><p><img src="http://bit.ly/WPnQD" title="fragmentation" alt="fragmentation" mce_src="http://bit.ly/WPnQD" border="1" height="350" width="1205"></p><p>&nbsp;</p><p>If you can't see the entire picture, it is at: <a href="http://bit.ly/WPnQD" title="http://bit.ly/WPnQD" target="_blank" mce_href="http://bit.ly/WPnQD">http://bit.ly/WPnQD</a></p><p>Each index is listed four times.<br></p><p>&nbsp;</p><p>&nbsp;</p>

And here's the results for the biggest table (58 columns... several of which are nvarchar(max)):
http://bit.ly/lkkAO
It has 28,696,234 records.

It can be the case when you have Auto Shrink for the database enabled or some shrink activity going on for the database. we tried to explain how ShrinkDb causes fragmentation and what is fragmentation at this site. (http://sqlbits.wordpress.com/2009/0...use-fragmentation-in-index-practical-example/)