Search This Blog

Thursday, April 19, 2012

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.


 


Wednesday, April 18, 2012

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