Tuesday, November 8, 2016

Understand Microsoft SQL Server Error 8946 and Fix it

SQL database error 8946 is related to the invalid page header. Malware infection, unexpected system shutdown, hardware issue are the main reasons for this error message. I read about this error on blogs and forums, and the user has different experience with it. A user complained that when he tried to run DBCC CHECKDB command then got the following error message:

Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:13280496) has invalid PFS_PAGE page header values.
Type is 0. Check type, alloc unit ID and page ID on the page.

Before moving to the error message, let’s talk about the PFS page.

PFS (Page Free Space) Page is the second page (Page Number 1) in the data file which is followed by the file header (Page Number 0). PFA page doesn’t have bit-map, it has byte-map. PFS interval is 8088 pages or about to 64MB.

DBCC CHECKDB uses PFS pages to determine the allocation status of pages. A User can repair SQL server error 8946 by two ways:

  • Restore from the backup
  • Repair PFS pages

Restore from the backup

Updated backup can save the work. Yes, this is first method that a user should try to fix this problem.

  • Open SQL Server Management Studio.
  • Click on Database folder and select Restore Database option


  •  Under ‘Source for Restore’ section, select ‘From device’ option and press the ellipsis button.
  • Set File as backup media and click on Add button.

  • Select the .Bak file and press the OK button.
In database dialog box, select the database name which you want to restore. If database is already existing, it will replace data from backup otherwise create the new one. Select the restore point which you want to use.

By this approach, the database can restore from backup.

Repair PFS page

A user cannot delete PFS page because it has a fixed part in the database. PFS pages cannot be rebuilt because it is not possible to identify the which page is allocated and which is not. I read Paul Randal’s blog and he said; he experimented various algorithms to rebuild PFA page with the optimistic or pessimistic setting and re-run the various consistency checks, but all required a very long runtime. Read here

A third-party tool

One more solution to fix this error is an SQL database repair tool. Stellar Repair for MS SQL software has the capability to solve this error. Download and install the software. Select the corrupt MDF file, click the Repair button to start repairing process.

Conclusion
We discuss the solutions here to fix Microsoft SQL database error 8946. I hope users will fix it after reading these methods. Always main the backup of database because it is the best way to save important data. You can share your thoughts and ask questions related to this error and I will try to provide you the best answer. 

No comments:

Post a Comment