How to re-construct PFS page in corrupted database

Question

Hi All,

I am troubleshooting one database in which some pages are corrupted. As Paul say we can re-construct the PFS_PAGE( please refer the below url), could you tell me step by step please how to re-reconstruct PFS pages?

 

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/02/04/checkdb-part-8-did-repair-fix-everything.aspx

My database is online and in multi user mode but if we query the data which belongs to corrupted pages, then it give error. Until we fix PFS_Page, we can't run DBCC with allow_data_loss or rebuild option.I am getting below error If I run DBCC CHECKDB with ALLOW_DATA_LOSS or REBUILD:

Msg 8946, Level 16, State 12, Line 1

Table error: Allocation page (1:2329344) has invalid PFS_PAGE page header values. Type is 0. Check type, alloc unit ID and page ID on the page.

 

Please tell me how can I take fix it? 

Thanks in Advance!!

Vikas

 

Answer 1

No - that is not what he said.  It is unrepairable:

PFS pages cannot be rebuilt because there is no infallible way to determine which pages are allocated or not.

Answer 2

Scott is correct, it is not possible to automatically have a PFS page repaired. In fact, there is no way inside of SQL Server to fix this type of issue.

While it is technically possible to reconstruct a PFS page, there are only 2 people I can think of off the top of my head that would be able to do this.

For academic purposes, what happens if you run the following query and ouput to a file (could you attach the file to this thread)? I'm interested to see the corruption.

DBCC TRACEON(3604)
DBCC PAGE('MyDatabaseName', 1, 2329344, 2)
DBCC TRACEOFF(3604)

---

Sean Gallardy | Blog | Twitter

Answer 3

Scott, I would interepret Paul's post as that there is a fine distinction between repair and reconstruct. Repair in this context means that you can do it automatically in DBCC CHECKDB.

Reconstruct on the other hand would mean that you do it manually by inspecting pages to see which are allocated and which are not. That is likely to be a trial-and-error process, not the least if the database have other corruption.

Vikas asked for a step-and-step instruction. First step is to check the funds for this project. Are they unlimited? That is, how much does it cost to lose these database entirely?

If the answer is that funds are unlimited, then consider to get in contact with a person like Paul Randall and see if they are available.

This is a task that requires good knowledge of the storage format SQL Server uses. Starting level requirement is that you have read Kalen Delaney's books, but not that is just the starting level. You also need experience, understand - and a whole lot time and patience.

Maybe there is a backup you can restore?

---

Erland Sommarskog, SQL Server MVP, [e-mail]