How do I correct Disconnected Overflow Pages in SQL 6.5

[redrovercmc]

I am using Microsoft SQL 6.5, SP 5a. When I run DBCC SHOWCONTIG one of my tables gives

...
- Disconnected Overflow Pages..................: 1
- Avg. Bytes free per Disconnected Ovfl page...: 1911.8
- Avg. Disconnected Overflow Page density......: 5.1

What do I do to correct this?

 

[mrdenny]

According to some very old documentation I found that value is reserved for future use.

How many pages does your table have? What's the scan density of the table?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[redrovercmc]

The DBCC Showcontig output is below. Does this help in solving the cause/resolution? Could this be an eronious error from running DBCC while changes are being made to the data?

DBCC SHOWCONTIG scanning 'block' table...
DBCC execution completed. If DBCC printed error messages, see your
System Administrator.

[SHOW_CONTIG - SCAN ANALYSIS]
---------------------------------------------------------------------------
Table: 'block' (640005311) Indid: 1 dbid:7
TABLE level scan performed.
- Pages Scanned................................: 2925
- Extent Switches..............................: 459
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 79.57% [366:460]
- Avg. Bytes free per page.....................: 505.3
- Avg. Page density (full).....................: 74.91%
- Overflow Pages...............................: 139
- Avg. Bytes free per Overflow page............: 1911.8
- Avg. Overflow Page density...................: 5.1%
- Disconnected Overflow Pages..................: 1
- Avg. Bytes free per Disconnected Ovfl page...: 1911.8
- Avg. Disconnected Overflow Page density......: 5.1

 

[mrdenny]

Based on the output you have provided everything appears fine with the table.

Do you get any error when running DBCC CHECKDB?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[redrovercmc]

My DBCC CHECKDB is giving the following message. I can't tell which table it relates to, so I don't know if it is for the same table.

Checking 8
The total number of data pages in this table is 5388.
The number of data pages in Sysindexes for this table was 71029. It
has been corrected to 5388.
The number of rows in Sysindexes for this table was 111078. It has
been corrected to 108860.
*** NOTICE: Space used on the log segment is 10.78 Mbytes, 0.47.
*** NOTICE: Space free on the log segment is 2298.34 Mbytes, 99.53.
Table has 108860 data rows.

 

[mrdenny]

I would assume that 8 is the object id from the sysobjects system table.

Look in sysobjects for that database and see if there is an object with the id of 8.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[redrovercmc]

The entry in sysobjects with an ID = 8 is the syslogs table. This is different from the table reported with a disconnected overflow page.
The DBCC did not report a disconnected overflow page this week. Is this an indication that it was an eronious error before?

Do you have any advice on the page and row mismatch reported and corrected by DBCC noted in my last entry? Does it look like something to be concerned about? If so, what can I do to correct it?

 

[mrdenny]

With SQL 6.5 it could be just about anything. You should run DBCC on a regular basis to ensure that there aren't more errors like this cropping up.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[redrovercmc]

I have been getting these page corrections reported in my weekly DBCC scripts for months. Any more help you can give?

 

[mrdenny]

Something internal to SQL Server isn't tracking the number of pages correctly in the sysindexes table. There isn't going to be anything that you can do about this except live with it as long as you are still on SQL 6.5.

If you were still getting this on a newer (SQL 2005+) version of SQL you could report it to Microsoft, but SQL 6.5 I can pretty much guarantee that they won't write a patch for as the product fell out of support years ago.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog