One
can check integrity issues by using DBCC CHECKDB (“DatabaseName”) and unfortunately
if DBCC CHECKDB returns some type of database corruption, then it can be solved
by executing DBCC CHECKTABLE. According to BOL “DBCC
CHECKTABLE: Checks the integrity of all the pages and structures that make up
the table or indexed view.”
Most
of the time it works fine for me at least, but last week it returned a
different error and failed to fix it.
DBCC results for 'MyTable'.
Msg 8914, Level 16, State 1, Line 1Incorrect PFS free space information for page (1:6294) in object ID 1325247776, index ID 1, partition ID 72057594860535808, alloc unit ID 72057594366853120 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
Error message
clearly showing that there is no actual corruption for said page. You can call
it just information that PFS (Page Free Space) entry has wrong calculation for
free space in page. Though PFS showing that page is empty (0%), but in reality
page is full (100%). And it is misleading free-space scanner, which finds it
full when try to insert data.
You can live happily with this error, but if you have applied a job to execute DBCC CHECKDB then you will keep on receiving job failure notice.
How to resolve it:
Three methods,
first, you should restore database from latest backup, which is error free. And
second method is to create a replica of culprit table, insert data into it,
delete existing (culprit table) and start enjoying new error free table.
(Once data copied
to new table and verified, don’t forget to check further errors by executing
DBCC CHECKDB.
Before trying above
two methods first go for third one. i.e. DBCC PAGE
DBCC PAGE ('YourDatabaseNameHere',
1, 6294, 1)
The best solution to resolve this error is to restore from backup. There are some other corruption errors which lead to severe corruption in SQL Server database. Restoration from backup & DBCC CHECKDB command solved most of the corruption errors & repair your corrupt database. But in case you don't have backup or DBCC CHECKDB command is unable to repair corrupt SQL Server database then you have only one solution to handle these severe corruption errors that is SQL Recovery Software. Please search on Google: SQL Database Repair Software
ReplyDelete