Assistance required in "fixing" an inconsistent database

[tg33]

I have a large database that the SQL Maintenance Plan (optimisations & reindexing) has started to fail on.
When the DBBCC Checkdb query is run against the affected database I get the following error

Server: Msg 8966, Level 16, State 5, Line 0
Could not read and latch page (1:177936) with latch type SH. PFS failed.
Server: Msg 8921, Level 16, State 1, Line 0
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is corrupt. Check previous errors.

I have copied the database to another server with a static 5GB temdb with no improvement.
I have run dbcc checks on the master db and the affected db's system tables with no errors reported
All dbcc command executed on this database and the suspect table fail.
I am also unable to DTS or BCP the data out from the suspect table to another DB or txt file as this eventually fails with an error

[Microsoft][ODBC SQL Server Driver][SQL Server]Attempt to fetch logical page (1:176464) in database 'ERRMS_Copy' belongs to object '1179151246', not to object 'tb_DeviceReadings'

I am unable to find an object with id '1179151246' in the affected database. I assume this indicates some form of allocation errors but all attempts to fix allocation fail.

Any advise or suggestions greatly appreciated

 

[JayKusch]

Have you tried running the sp_checkdbtempsize procedure to find out how much tempdb space is required by CHECKDB to be able to run successfully?

Thanks

J. Kusch

 

[tg33]

JayKusch

Thanks for the tip. Tried that and plenty of size in tempdb for the task. Still no better

Regards

Tom

 

[SQLRickster]

It sounds like there may be an index that has been corrupted. Try executing

DBCC UPDATEUSAGE
Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure. This may be faking out the DBCC making it believe that you don't have enough space in temp db.

Syntax is:
A. Update sysindexes by specifying 0 for the current database
This example specifies 0 for the database name and Microsoft® SQL Server™ reports information for the current database.

DBCC UPDATEUSAGE (0)
GO

B. Update sysindexes for pubs, suppressing informational messages
This example specifies pubs as the database name, and suppresses all informational messages.

DBCC UPDATEUSAGE ('pubs') WITH NO_INFOMSGS
GO

C. Update sysindexes for the authors table
This example reports information about the authors table.

DBCC UPDATEUSAGE ('pubs','authors')
GO

D. Update sysindexes for a specified index
This example uses the index name, UPKCL_auidind.

DBCC UPDATEUSAGE ('pubs', 'authors', 'UPKCL_auidind')


DBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes. Size information is not maintained for nonclustered indexes.

If there are no inaccuracies in sysindexes, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and the WITH NO_INFOMSGS option is not used, UPDATEUSAGE returns the rows and columns being updated in sysindexes.

Use UPDATEUSAGE to synchronize space-usage counters. DBCC UPDATEUSAGE can take some time to run on large tables or databases, so it should typically be used only when you suspect incorrect values returned by sp_spaceused. sp_spaceused accepts an optional parameter to run DBCC UPDATEUSAGE before returning space information for the table or index.

If this does not find and or correct the problem the database may have a torn page.... At that point a restore of the last know good backup is my only suggestion. Maybe someone else has a better solution...

Good Luck...
SQLRickster.