SQL Server: Incorrect PFS free space information for page (x:xxxx) in object ID xxxx

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 1
Incorrect 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)

SQL SERVER Log: This instance of SQL Server has been using a process ID of xxxx since mm/dd/yyyy

If you call yourself SQL Server DBA, then you must be able to interpret SQL Server Log, One of a common information message captured by SQL Server Log is

This instance of SQL Server has been using a process ID of 2308 since 5/3/2012 2:30:52 AM (local) 5/3/2012 6:30:52 AM (UTC). This is an informational message only; no user action is required.

(process id and time would be different every time)

First thing to note about this log entry is that it’s just an information message and no user action is required. Mean NO NEED TO WORRY. This is just an information message that SQL Server instance using a process id (in my case it is 2308), since SQL Server services are started (in my case SQL Server services are started at 5/3/2012 2:30:52 AM) and after a month instance is still running.

SQL Server creates a log entry for this message on each date change. So you can find one entry for each 24 hours.

To verify this process id, open Task Manager and move to “Processes” tab, click on “View” in menu, “Select Columns”. Select PID (Process Identifier). Now it will start showing process identifier for each process. Check process identifier for sqlserver.exe

SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete

This morning, while going through my regular SQL Server Logs reports, for one of our production server, I found a different error.

SQL Server has encountered 52 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TempDB.mdf] in database [TempDB] (2). The OS file handle is 0x00000884. The offset of the latest long I/O is: 0x00000457490000

First thing that I searched about this error was that is this a critical message?
And answer I found was YES. Basically when talk about I/O in SQL Server, we always have measurements of mille seconds in our mind and waits of several seconds is considered too odd. SQL Server I/O wait time can be examined by following query:

SELECT  *

FROM    sys.dm_os_wait_stats

WHERE   wait_type LIKE ‘PAGEIOLATCH%’

How to check you hard drive performance?

To check, server IO subsystems I trust on Performance Monitor IO Counter PhysicalDisk Object: Avg. Disk Queue Length. Monitor this counter for at least 10 minutes. If the Avg. Disk Queue Length exceeds 2 for next ten minutes for each individual disk drive in an array, then it is sure that you have IO bottleneck.

Who is the culprit, SQL Server or Operating System?

Problem is only your SAN or Local disk IO subsystem. In my case, I found that few other applications were also installed by client on same drive and which were pushing SQL Server to wait for too long to complete its IO requests.