SQL DBA - Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information

Errors :-  

When u Run DBCC CHECKDB you will get a Error like this :-

 

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:278) in object ID 1047674780, index ID 0, partition ID 72057594052018176, alloc unit ID 72057594065125376 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

There are 701 rows in 359 pages for object "XXXxxxXXXxxxXXX".

CHECKDB found 0 allocation errors and 6 consistency errors in table 'XXXxxxXXXxxxXXX' (object ID 1047674780).

Solution For this Error :-

             1. Check which Table is corrupted using.

  DBCC Checktable ('Your_Table_Name’).

2.Run this Query to Change DB into Single User Mode.

  ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

3.Run this Query to Recover the Table/DB. 

 DBCC CheckTable ('corrupted_table', REPAIR_ALLOW_DATA_LOSS)

4.Once this Execution Completed Sql returns “Errors are Corrected” .

5.Run this Query to Change the DB into MultiUser Mode.

 ALTER DATABASE corrupted_db SET MULTI_USER

Here are the technical details of the problem :

The issue was that when minimal logging for LOBs was used (under the SIMPLE recovery model, during BULK INSERT/BCP/Large insert with TABLOCK), extents are being pre-allocated and the pages were being marked 100% full in the PFS page when the pages were allocated. The idea was that all pages eventually will be filled up with LOB data, and by marking them full during allocation we avoid an extra update to the PFS pages when the data is actually put on the page.

Suppose now that 64 pages are pre-allocated, and only 40 or so pages are used and have rows on them. When the transaction commits, the Storage Engine is supposed to deallocate the 24 extra pages that were pre-allocated, and marked 100% full, even though they don’t have any rows on them yet. There was a bug where in a certain case the deallocation wouldn't happen, so you end up with empty pages that have a PFS state of 100%, but don’t have any rows on them.

In general, even though this is a bug in the code, functionality wise, there is nothing wrong with the database, besides a number of additional pages that are empty and allocated to the LOB tree, so as long as the message tells you that the page is supposed to be empty, but is actually marked 100%, nothing can really go wrong with that page. Unfortunately, DBCC will keep reporting these errors.

Although I haven't tried this, my guess is that you can get rid of the empty LOB pages using ALTER INDEX ... REORGANIZE WITH (LOB_COMPACTION = ON). So, if you see some of these errors with no other errors, you may have hit this bug and have nothing really to worry about.

 

SQL DBA - TSQL to find all TSQL/Commands running on a server at a time?

SELECT sqltext.TEXT,

req.session_id,

req.status,

req.command,

req.cpu_time,

req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

SQL DBA - TSQL to get current location of all data and log files of all databases of the server

SELECT name, physical_name AS current_file_location

FROM sys.master_files

SQL DBA - Get Complete Information of a SQL Server

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

CONVERT(CHAR(100), SERVERPROPERTY('ProductVersion')) AS ProductVersion,

CONVERT(CHAR(100), SERVERPROPERTY('ProductLevel')) AS ProductLevel,

CONVERT(CHAR(100),SERVERPROPERTY('ResourceLastUpdateDateTime'))AS ResourceLastUpdateDateTime,

CONVERT(CHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion,

CASE

WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1

      THEN 'Integrated security'

WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0

      THEN 'Not Integrated security'

END AS IsIntegratedSecurityOnly,

CASE WHEN SERVERPROPERTY('EngineEdition') = 1

      THEN 'Personal Edition'

WHEN SERVERPROPERTY('EngineEdition') = 2

      THEN 'Standard Edition'

WHEN SERVERPROPERTY('EngineEdition') = 3

      THEN 'Enterprise Edition'

WHEN SERVERPROPERTY('EngineEdition') = 4

      THEN 'Express Edition'

END AS EngineEdition,

CONVERT(CHAR(100), SERVERPROPERTY('InstanceName'))AS InstanceName,

CONVERT(CHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))AS ComputerNamePhysicalNetBIOS,

CONVERT(CHAR(100), SERVERPROPERTY('LicenseType'))AS LicenseType,

CONVERT(CHAR(100), SERVERPROPERTY('NumLicenses'))AS NumLicenses,

CONVERT(CHAR(100), SERVERPROPERTY('BuildClrVersion'))AS BuildClrVersion,

CONVERT(CHAR(100), SERVERPROPERTY('Collation'))AS Collation,

CONVERT(CHAR(100), SERVERPROPERTY('CollationID'))AS CollationID,

CONVERT(CHAR(100), SERVERPROPERTY('ComparisonStyle'))AS ComparisonStyle,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('EditionID')) = -1253826760

      THEN 'Desktop Edition'

WHEN SERVERPROPERTY('EditionID') = -1592396055

      THEN 'Express Edition'

WHEN SERVERPROPERTY('EditionID') = -1534726760

      THEN 'Standard Edition'

WHEN SERVERPROPERTY('EditionID') = 1333529388

      THEN 'Workgroup Edition'

WHEN SERVERPROPERTY('EditionID') = 1804890536

      THEN 'Enterprise Edition'

WHEN SERVERPROPERTY('EditionID') = -323382091

      THEN 'Personal Edition'

WHEN SERVERPROPERTY('EditionID') = -2117995310

      THEN 'Developer Edition'

WHEN SERVERPROPERTY('EditionID') = 610778273

      THEN 'Enterprise Evaluation Edition'

WHEN SERVERPROPERTY('EditionID') = 1044790755

      THEN 'Windows Embedded SQL'

WHEN SERVERPROPERTY('EditionID') = 4161255391

      THEN 'Express Edition with Advanced Services'

END AS ProductEdition,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('IsClustered')) = 1

      THEN 'Clustered'

WHEN SERVERPROPERTY('IsClustered') = 0

      THEN 'Not Clustered'

WHEN SERVERPROPERTY('IsClustered') = NULL

      THEN 'Error'

END AS IsClustered,

CASE WHEN CONVERT(CHAR(100),SERVERPROPERTY('IsFullTextInstalled')) = 1

      THEN 'Full-text is installed'

WHEN SERVERPROPERTY('IsFullTextInstalled') = 0

      THEN 'Full-text is not installed'

WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL

      THEN 'Error'

END AS IsFullTextInstalled,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSet'))AS SqlCharSet,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSetName'))AS SqlCharSetName,

CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrder'))AS SqlSortOrderID,

CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrderName'))AS SqlSortOrderName

ORDER BY CONVERT(CHAR(100), SERVERPROPERTY('Servername'))