Consistency Errors due to MSG 8914 – PFS free space

Issue

While running DBCC CHECKDB() job on SQL 2008 R2 SP1 with SQL 2005 compatibility Level databases, it failed with the below DBCC results for ‘MESTEMPLATEDOCUMENTS’.

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:87991) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:88697) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91929) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91931) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91932) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

There are 0 rows in 1 pages for object “MESTEMPLATEDOCUMENTS”.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS'(object ID 1394416337).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (dbo.MESTEMPLATEDOCUMENTS’).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Resolution

After a Brief research found this solution from Paul Randall
http://www.sqlskills.com/blogs/paul/corruption-bug-that-people-are-hitting-msg-8914-pfs-free-space/

As solution didn’t work, I had to go ahead and do the repair

Implementing the repair on Dev or Test first to make sure the repair doesn’t cause any unintended results. Please take a Backup of the Database on which you would be working

Steps Followed:
1) Run DBCC Checktable(tablename) to verify if the issue is still occurring
2) If it does, query the table to see if you have any data in there to gauge the data loss after
the repair.
3) The table in question is empty
4) Change DB to “Single User Mode”
USE [master]
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
5) Run the below to correct the errors
DBCC CHECKTABLE (MESTEMPLATEDOCUMENTS, REPAIR_ALLOW_DATA_LOSS )

Results are as follows

with All_ErrorMsgs DBCC results for ‘MESTEMPLATEDOCUMENTS’.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:87991) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:88697) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91929) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91931) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:91932) in object ID 1394416337, index ID 1, partition ID 72057602418868224, alloc unit ID 72057601377828864 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.
The error has been repaired.
There are 0 rows in 1 pages for object “MESTEMPLATEDOCUMENTS”.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS’ (object ID 1394416337).
CHECKTABLE fixed 0 allocation errors and 5 consistency errors in table ‘MESTEMPLATEDOCUMENTS’ (object ID 1394416337).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

6) Change DB back to Multi-User mode
USE [master]
GO
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

7) To verify the results we can run the DBCC Checktable(tablename) command again and if no issues results will be as below

Consistency_Erorrs_and_PFS_space

Advertisements
Posted in Database Corruption, MSSQL | Leave a comment

Modifying maxAllowedContentLength

Issue:

 “java.io.FileNotFoundException: http://dev-perfdb2/olap/msmdpump.dll” is thrown by an application due to default configuration in the IIS sever which throws an error when the content length of the data (the data which is passed from an application to DB is passed through the IIS) is too large.

Resolution: So to subdue this error we need to increase the maxAllowedContentLength on the IIS server where OLAP is setup
OS: Windows 2012
SSAS : SQL 2012
Web server: IIS 8.0

1) Open IIS Manager
2) Highlight “LocalHost”
3) Double click “Request Filtering”
4) Select “Hidden Segments” tab and right click “web.Config” and select “Edit Feature
Settings”, it opens up “Edit Request Filtering Settings” change “Maximum allowed
content length” to “4294967295(3.99GB)”
5) Restart IIS.

Posted in IIS, MSSQL | Leave a comment

Providing Permissions for Non-Admin users to use Activity Monitor

Windows 2012 and SQL 2012

To grant permission for Non-Admin users to use Activity Monitor on an SQL 2012 server, follow the below
1) Add [Domain\Username] to [Computer Management] –> [Performance Monitor Users]
2) Run the below script on SQL Instance level for the User that your are granting access to

USE master;
Go
GRANT VIEW SERVER STATE TO [Domain\Username];
GO
GRANT VIEW ANY DEFINITION TO [Domain\Username];
GO
GRANT ALTER ANY DATABASE TO [Domain\Username];
GO
GRANT CREATE DATABASE TO [Domain\Username];
GO

Posted in MSSQL, Permissions | Leave a comment