Stored procedure corruption - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Stored procedure corruption

Author

Topic

phouse
Starting Member

5 Posts

Posted - 2009-11-04 : 18:35:43

I've run into a very weird corruption issue and I have been unable to resolve it. It's as follows: one of my database's stored procedures are unaccessible. The error when I attempt to expand the stored procedures reads, "Failed to retrieve data for this request. Database ID 12, page(1:171392) slot 0 for LOB data type node does not exist".

I've attempted to run DBCC CHECKDB against the database and I've received the error message, "Msg 605, Level 12, State 3, Line 1
Attempt to fetch logical page (1:170880) in database 13 failed. It belongs to allocation unit 72057601056702464 not to 281474980642816.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors".

I am at a loss. Any assistance is appreciated.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-11-05 : 01:08:28

Error message seems to indicate that there's corruption in one of the system tables. This is not repairable. If certain of the system tables are damaged, checkDB can't tell what the DB should look like so it can't tell what's damaged or how to fix it.

Restore from a clean backup.
Don't forget to do some root-cause analysis after getting this fixed.

--
Gail Shaw
SQL Server MVP

phouse
Starting Member

5 Posts

Posted - 2009-11-09 : 13:02:58

Yikes! This is what I'm afraid of. Unfortunately, a clean back up is likely going to result in data loss unless I can reliably import the delta from the current DB to a restored DB. I've done more searching and this is what I've found (for archive/information purposes).

I ran the query detailed here: http://msdn.microsoft.com/en-us/library/aa337419.aspx


USE database_name;
GO
SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name,
au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id
WHERE au.allocation_unit_id = allocation_unit_id OR au.allocation_unit_id = allocation_unit_id
ORDER BY au.allocation_unit_id;
GO

The results returned are:
281474980642816 sysobjvalues PRIMARY IN_ROW_DATA 6363 1
72057601056702464 JournalDetailAPSubLedger PRIMARY IN_ROW_DATA 1314 1

Running checkdb with physical_only returned the following error:
Msg 605, Level 12, State 3, Line 1
Attempt to fetch logical page (1:170880) in database 13 failed. It belongs to allocation unit 72057601056702464 not to 281474980642816.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Location: tmpilb.cpp:2530
Expression: fFalse
SPID: 55
Process ID: 2036
Description: Attempt to access expired blob handle (3)
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details

I'm not sure if this yields anything of consequence, I'm trying to understand how this situation in greater detail.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-11-10 : 16:07:50

There's not really that much more detail of use. SQL's trying to fetch a page and it finds that it's part of a different allocation unit than it should be. This cannot be repaired when it involves the system tables. There's no documentation on the SQL 2005/2009 system tables, so it's hard to say what's in the table sysobjvalues (the one that's affected). Guessing, from the errors and the description you gave, it's related to the stored code of the stored proces

You can either restore from a clean backup (and roll the log backups forward to restore right up to date if you have log backups) or you can try and export all the data, script all the objects, create a new database and reload everything. It's highly likely that something will fail to script or export because of the corruption.

--
Gail Shaw
SQL Server MVP

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-10 : 18:47:01

may try restoring a backup to a different db, then in the original db, drop the proc and copy it over from the restored copy. the drop proc may fail as well however with the same error.

if it succeeds, run dbcc checkdb again after to make sure nothing else is wrong.

as Gail said, may need to bcp all the data out, restore backup and bulk insert it back in

phouse
Starting Member

5 Posts

Posted - 2009-11-12 : 13:50:58

Thanks for all the information. Scripting out the data and restoring the stored procedures from a source outside the database is the solution path we're going with. We placed a call with Microsoft support to ensure we're proceeding appropriately and they've been very helpful.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-11-13 : 04:39:19

Good luck.

May I suggest, once you're running again, set up regular checkDB jobs. You want to be able to catch this kind of thing early enough that you can restore from backup to fix it if necessary.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources