SQL Server: Script to Generate HTML Report/mail for Databses Current Size, Growth Rate and Available Disk Space

Working with multiple databases on multiple instances is a tough job. You need to monitor all these instances for everything. Best way to keep eye on every instance activity is SQL Server Jobs.
How fastly databases on these instances are growing and does target instance has requried space on hard drives ? These are basic questions which every DBA keeps in mind during instance monitoring.
Amna Asif has suggested a better script to create a proper report (to mail) for actual database space, requried and currently available on hard drives.

 Script to send an alert through mail, with information that how many drive
 space is required from next databases growth on a specific instance and how many
 space is available.

 Script By: Amna Asif for ConnectSQL.blogspot.com


 DECLARE@dbName varchar(200),
    @Qry Nvarchar(max)
 DECLARE@dbsize VARCHAR(50),
    @logsize VARCHAR(50),
    @reservedpages VARCHAR(50),
    @usedpages VARCHAR(50),
    @pages VARCHAR(50)

 SET @dbName =

—Get LOG File Spaces of All Databases–
 CREATE TABLE #LogSpaceStats
      dbName SYSNAME,
      Totallogspace DEC(20, 2),
      UsedLogSpace DEC(20, 2),
      Status CHAR(1)
        ( dbName, Totallogspace,UsedLogSpace, Status)
        EXEC ( ‘DBCC sqlperf(logspace) WITH NO_INFOMSGS’
–Get Info of All Drives
      RowID intIDENTITY
      Drive char,
      DriveSpace varchar(100),
      Required_Space varchar(100)
 INSERT INTO @ServerDrives
        ( Drive, DriveSpace )
        EXEC master.sys.xp_fixeddrives
–Temporary Table to hold requried data
 CREATE TABLE#ServerFileStats
      dbName SYSNAME,
      Database_DSize varchar(100),
      Allocated_Space varchar(100),
      Unallocated_Space varchar(100),
      Unused varchar(100),
      Database_LSize varchar(100),
      UsedLogSpace DEC(20, 2),
      FreeLogSpace DEC(20, 2),
      FDataFileGrowth DEC(20, 2),
      FLogFileGrowth DEC(20, 2),
      DataFileDrive char,
      LogFileDrive char
–Cursor Used to get each database size on given instance
        WHERE   state_desc =‘ONLINE’
                ANDis_read_only = 0
 FETCH NEXT FROM cur_dbName into @dbName
 WHILE @@FETCH_Status = 0
        SELECT  @Qry = ‘ SELECT @dbsizeOUT = sum(convert(bigint,
                              case when status & 64 = 0 then size
                              else 0 end))
                              ,@logsizeOUT = sum(convert(bigint,
                                    case when status & 64 <> 0 then size
                                    else 0 end)) 
                                       FROM ‘ +@dbName + ‘.dbo.sysfiles ‘
        EXEC sp_executesql@Qry,
            N’@dbsizeOUT  nvarchar(50) OUTPUT,@logsizeOUT  nvarchar(50) OUTPUT’,
            @dbsizeOUT =@dbsize OUTPUT,@logsizeOUT = @logsize OUTPUT; 

        SELECT  @Qry = ‘ SELECT @reservedpagesOUT = sum(a.total_pages)

                                 ,@usedpagesOUT = sum(a.used_pages)
                      FROM ‘ +@dbName + ‘.sys.partitions p join ‘ + @dbName
                +‘.sys.allocation_units a on p.partition_id = a.container_id 
                      LEFT JOIN ‘ +@dbName
                +‘.sys.internal_tables it on p.object_id = it.object_id’

        EXEC sp_executesql@Qry,

     N’@reservedpagesOUT  nvarchar(50) OUTPUT,@usedpagesOUT nvarchar(50) OUTPUT’,
            @reservedpagesOUT = @reservedpages OUTPUT,
            @usedpagesOUT = @usedpages OUTPUT ; 
        SELECT  @Qry = ‘ INSERT INTO #ServerFileStats                
                         SELECT DB_size.Database_Name
                         , DB_size.Database_DSize
                         , DB_size.Allocated_Space
                         , DB_size.Unallocated_Space
                         , DB_size.Unused
                         , DB_size.Database_LSize
             , (lss.TotalLogSpace*(lss.UsedLogSpace/100)) UsedLogSpace
             , (TotalLogSpace-(TotalLogSpace*(UsedLogSpace/100))) FreeLogSpace
             ,CASE mfD.is_percent_growth
              WHEN 0 THEN CONVERT(DEC(15,2),(mfD.growth* 8192 / 1048576))
              ELSE CONVERT(DEC(15,2),(CONVERT(DEC(15,2),REPLACE(DB_size.Database_DSize,” MB”,””))
                              *mfD.growth/100)) END  FDataFileGrowth
                          CASE mfL.is_percent_growth WHEN 0 THEN CONVERT(DEC(15,2),(mfL.growth* 8192 / 1048576))
                          ELSE CONVERT(DEC(15,2),(CONVERT(DEC(15,2),REPLACE(DB_size.Database_DSize,” MB”,””))
                          *mfL.growth/100)) END  FLogFileGrowth
                         ,LEFT(mfD.physical_name,1) DataFileDrive
                         ,LEFT(mfL.physical_name,1) LogFileDrive
                          SELECT Database_Name = ”’ + @dbName
, Database_DSize = ltrim(str((convert (dec (15,2),’
       + ‘))* 8192 / 1048576,15,2) + ” MB”)
, ”Allocated_Space”=ltrim(str((CASE WHEN ‘
       +@dbsize + ‘ >= ‘+ @reservedpages
THEN convert (DEC (15,2),’
                +‘)* 8192 / 1048576
ELSE 0 end),15,2) + ” MB”) 
                                    , ”Unallocated_Space” = ltrim(str((CASE WHEN ‘
               +@dbsize + ‘ >= ‘+ @reservedpages
THEN  (convert (DEC (15,2),’
                +@dbsize + ‘) – convert (DEC (15,2),’ + @reservedpages
                +‘))* 8192 / 1048576
ELSE 0 end),15,2) + ” MB”)
                                    , ”Unused” =ltrim(str(((‘
                +@reservedpages + ‘ – ‘+ @usedpages
                +‘) * 8192 / 1024.)/1024,15,2) + ” MB”) 
                , Database_LSize = ltrim(str((convert (dec (15,2),’
                + ‘))* 8192 / 1048576,15,2) + ” MB”)
  )DB_size LEFT JOIN #LogSpaceStats AS lss on lss.dbName=DB_size.Database_Name
                          INNER JOIN ‘ +@dbName
                +‘.sys.databases db ON DB.name=DB_size.Database_Name
                          INNER JOIN ‘ +@dbName
                +‘.sys.master_files mfD on mfD.database_id=DB.database_id AND mfD.type_desc=”ROWS”
                          INNER JOIN ‘ +@dbName
                +‘.sys.master_files mfL on mfL.database_id=DB.database_id AND mfL.type_desc=”LOG”’

        EXEC ( @Qry


  FETCH NEXT FROM cur_dbName into @dbName


 SET    Required_Space =SumDriveS.sumofdrivespcae
 FROM   ( SELECT    SUM(CONVERT(DEC(20, 2), sumofdrivespcae)) sumofdrivespcae,
                    DRIVE AS DRIVE
          FROM      ( SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FDataFileGrowth, ‘ MB’, )))
                                fss.DataFileDrive ASDRIVE
                      FROM      #ServerFileStats fss
                      GROUPBY  fss.DataFileDrive
                      SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FLogFileGrowth, ‘ MB’, )))
                                fss.LogFileDrive AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUPBY  fss.LogFileDrive )SumDrive
          GROUPBY  SumDrive.DRIVE )SumDriveS
        LEFT OUTER JOIN@ServerDrives sd on SumDriveS.Drive = sd.Drive

—————————————–Report Mailing———————–
DECLARE @Loop int
 DECLARE@Subject varchar(100)
 DECLARE@strMsg varchar(4000)

 SELECT@Subject = ‘SQL Monitor Alert: ‘ + @@SERVERNAME+ ‘        ‘

        + Convert(varchar, GETDATE())
  Declare @Body varchar(max),
    @TableHead varchar(1000),
    @TableTail varchar(1000),
    @TableHead2 varchar(1000),
    @Body2 varchar(3000)
 Set NoCount On ;
— Create HTML mail body
 Set @TableTail = ‘</table></body></html>’;
  Set @TableHead = ‘<html><head>’+ ‘<style>’
    + ‘td {border: solid black 1px;padding-left:3px;padding-right:3px;padding-top:2px;padding-bottom:2px;font-size:10pt;} ‘
    + ‘</style>’ + ‘</head>’
    + ‘<body><table cellpadding=0 cellspacing=0 border=0>’
    + ‘<tr><td align=center bgcolor=#E6E6FA><b>Row ID</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>Database Name</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>File Group</b></td>’
    + ‘<td align=center bgcolor=#5F9EA0><b>DF Total Space</b></td>’
    + ‘<td align=center bgcolor=#5F9EA0><b>DF Allocated Space</b></td>’
    + ‘<td align=center bgcolor=#5F9EA0><b>DF Unallocated Space</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>DF Unused</b></td>’
    + ‘<td align=center bgcolor=#5F9EA0><b>LF Total Space</b></td>’
    + ‘<td align=center bgcolor=#5F9EA0><b>LF Used Space</b></td>’
    + ‘<td align=center bgcolor=#5F9EA0><b>LF Unused Space</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>DF FileGrowth</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>LF FileGrowth</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>DF Drive</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b> LF Drive </b></td></tr>’ ;

  Select @Body =( SELECT    td = CONVERT(VARCHAR, ROW_NUMBER() OVER ( ORDER BY dbName ))

                            + CHAR(10),
                            td = ISNULL(dbName, ‘Unknown’) + CHAR(10),
                            td = ISNULL(‘Data/LOG’, ‘Unknown’) + CHAR(10),
                            td = ISNULL(Database_DSize, ‘0.00’) + CHAR(10),
                            td = ISNULL(Allocated_Space, ‘0.00’) + CHAR(10),
                            td = ISNULL(Unallocated_Space, ‘0.00’) + CHAR(10),
                            td = ISNULL(Unused, ‘0.00’) + CHAR(10), ,
                            td = ISNULL(Database_LSize, ‘0.00’) + CHAR(10),
                            td = ISNULL(convert(varchar, UsedLogSpace), ‘0.00’)
                            +‘ MB’ + CHAR(10),
                            td = ISNULL(convert(varchar, FreeLogSpace), ‘0.00’)
                            + ‘ MB’ + CHAR(10),
                            td = ISNULL(convert(varchar, FDataFileGrowth),
                                        ‘0.00’) + ‘ MB’ + CHAR(10), ,
                            td = ISNULL(convert(varchar, FLogFileGrowth),
                                        ‘0.00’) + ‘ MB’ + CHAR(10), ,
                            td = ISNULL(DataFileDrive, ‘0’) + CHAR(10), ,
                            td = ISNULL(LogFileDrive, ‘0’) + CHAR(10),
                  FROM      #ServerFileStats
                  ORDERBY  dbName
        FOR       XML RAW(‘tr’),

— Replace the entity codes and row numbers
 Set @Body = Replace(@Body, ‘_x0020_’, space(1))
 Set @Body = Replace(@Body, ‘_x003D_’, ‘=’)
 Set @Body = Replace(@Body, ‘<tr><TRRow>1</TRRow>’, ‘<tr bgcolor=#C6CFFF>’)
 Set @Body = Replace(@Body, ‘<TRRow>0</TRRow>’,)


 SELECT @flag = 1
 FROM   @ServerDrives
 WHERE  convert(dec(15, 2), DriveSpace) < convert(dec(15, 2), Required_Space)
        * 2
 SET @flag = ISNULL(@flag, 0)

 SET@TableHead2 = ‘<html><head>’+ ‘<style>’

    + ‘td {border: solid black 1px;padding-left:1px;padding-right:1px;padding-top:1px;padding-bottom:1px;font-size:8pt;} ‘
    + ‘</style>’ + ‘</head>’
    + ‘<body><table cellpadding=0 cellspacing=0 border=0>’
    + ‘<tr><td align=center bgcolor=#E6E6FA><b>Row ID</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>Drive</b></td>’
    + ‘<td align=center bgcolor=#E6E6FA><b>Drive Space</b></td> ‘

 IF ( @flag = 0 )
    set@TableHead2 = @TableHead2
     + ‘<td align=center bgcolor=#E6E6FA><b>Required Drive Space</b></td></tr>’ ;
    set@TableHead2 = @TableHead2
     + ‘<td align=center bgcolor=#FF7F50><b>Required Drive Space</b></td></tr>’ ;
 Select @Body2 =( SELECT   td = ROW_NUMBER() OVER ( ORDER BY Drive ),
                            td = ISNULL(Drive, ‘Unknown’) + char(10),
                            td = ISNULL(DriveSpace + ‘ MB’, 0) + char(10),
                            td = ISNULL(Required_Space + ‘ MB’, 0)
                   FROM     @ServerDrives sd
        For        XML RAW(‘tr’),

 Select @Body = @TableHead2 +@Body2 + @TableTail +‘<br/><br/><br/><br/>’

        +@TableHead + @Body +@TableTail
— Send mail
 EXEC msdb.dbo.sp_send_dbmail
      @recipients = ‘abc@xyz.com’,
    @subject =@Subject,
    @profile_name =‘MyMailProfileName’,
    @body =@Body,
    @body_format =‘HTML’ ;

 –Drop Temporary Tables When Not Required
 DROP TABLE #ServerFileStats
 DROP TABLE#LogSpaceStats

SQL Server: Limit (MySQL) equal Function in SQL Server

In MySQL, Limit is a powerful function used with SELECT query to return rows BUT within given range. We commonly need it when returning only required rows to application according to paging range. For example to return rows from 11 to 15 we will use following query in MySQL


In SQL Server, same functionality can be achieved with three different ways.

1.       With Derived Table (Most inefficient way, but applicable to all versions of SQL Server)


      FROM    ( SELECT    *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
          FROM      MyTable ) DerivedTable
       WHERE   RowNum >= 11
        ANDRowNum <= 11 + ( 5 1 )

2.       With CTE Common Table Expression (Applicable to SQL Server 2005/2008/2012)
     WITH   CTE
          AS ( SELECT   *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
               FROM     MyTable)
    SELECT  *
    FROM    CTE
    WHERE   RowNum >= 11
            ANDRowNum <= 11 + ( 5 1 )

3.       With OFFSET_ROW FETCH (Applicable to SQL Server 2012 Only)
        Here “OFFSET means, how many rows to skip, “ROWS FETCH NEXT” means, how many rows to skip

 SELECT * FROM MyTable  

        OFFSET 10

SQL Server: How Local Variables Can Reduce Query Performance

It’s a common practice by database developers to use local variables in stored procedures and scripts to place filter on basis of these local variables. YES, these local variables can slowdown your queries. Let’s prove it.
Create a new table and insert dummy rows.


      (tempID UNIQUEIDENTIFIER,tempMonth INT,tempDateTime DATETIME )

INSERT INTO TempTable (tempID, tempMonth, tempDateTime)

SELECT NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()
GO 100000 — (EXECUTE THIS BATCH 100000 TIME)

— Create an index to support our query

CREATE NONCLUSTERED INDEX[IX_tempDateTime] ON [dbo].[TempTable]
([tempDateTime] ASC)
Now let’s execute a simple query with hard coded values in WHERE clause


WHEREtempDateTime > ‘2012-07-10 03:18:01.640’
Table ‘TempTable’. Scan count 1, logical reads 80,physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Check out its execution plan and index seeks properties. You can find that estimated rows are double to actual rows but that’s not a big difference to affect execution plan and resultantly optimizer has selected a proper plan to execute this query.

Query optimizer has estimated number of rows from its base statistics histogram i.e.  EQ_ROWS + AVG_RANGE_ROWS (77 + 88.64286)

DBCC SHOW_STATISTICS (‘dbo.TempTable’, IX_tempDateTime)

Now, let’s modify our SELECT query and use local variable and execute it. You will find that query optimizer has selected a different plan this time, a more costly plan. WHY ??

SET@RequiredDate = ‘2012-07-10 03:18:01.640’

WHEREtempDateTime  >@RequiredDate
Table ‘TempTable’. Scan count 1, logical reads 481, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Huge difference of estimated and actual number of rows clearly indicating that query optimizer was unable to proprly estimate number of rows and with this wrong estimation, it has selected a more costly execution plan. Basically Query Optimizer does not know the value of local variable at the time of optimization and resultantly can’t use histogram of statistics. It behaves differently with inequality and equality operators.

In Case of Inequality Operator:
In our case of inequality operator in query, query optimizer used a simple formula of 30% of total rows.

Estimated Rows =(Total Rows * 30)/100 = (100000*30)/100 = 30000

 In Case of Equality Operator:

SET@RequiredDate = ‘2012-07-10 03:18:01.640’

WHEREtempDateTime  =@RequiredDate

If equality operator is used with local variables, query optimizer gets estimated rows figure from a different formula i.e.  Density * Total Number of Table Rows. Execute following query to get density value.

DBCC SHOW_STATISTICS (‘dbo.TempTable’, IX_tempDateTime)

All Density = 0.0007358352

Total Number of Rows in Table = 100000
Estimated Rows = Density * Total Number =  0.0007358352 *  100000 = 73.5835

Drop table when not required


SQL Server: Smarter Way of Query Load Testing at Testing Server

Most import decision by Database Administrator is that a query on development server, where only hundred or thousand of rows exists, can also perform flawless according to given benchmarks when we will deploy same query on production server, where number of rows could be in millions.

One solution is to insert millions of rows in testing environment and then check execution plan. But it’s really painful.

Thanks to SQL Server, which has provided a better solution, since SQL Server 2005. Yes, you can test a query that base table contains only dozen of rows but can act like they have million of rows (or as much as you want). Let’s try with a simple query at Adventure Works.

SELECT  p.ProductID, p.Name, pm.Name AS ProductModel, pmx.CultureID,
FROM    Production.Product AS p
        INNER JOIN Production.ProductModel AS pm
        ON p.ProductModelID = pm.ProductModelID
        INNER JOIN Production.ProductModelProductDescriptionCulture AS pmx
        ON pm.ProductModelID = pmx.ProductModelID
        INNER JOIN Production.ProductDescription AS pd
        ON pmx.ProductDescriptionID =pd.ProductDescriptionID
WHERE   pm.Name = ‘Road-150’

How many rows each table (in above query) contains, check with following query.

SELECT OBJECT_NAME(object_id),rows FROM sys.partitions

WHERE object_id IN
object_id(‘Production.ProductModelProductDescriptionCulture’) ,
object_id(‘Production.ProductDescription’) ,
AND index_id = 1

On execution of first query, you can find that in execution plan, SQL Server Optimizer took number of rows estimate from its table statistics and its showing correct estimated and actual number of rows.

Now we will deceive SQL Server Optimizer for number of rows of ‘Production.Product’ table. Simple use following update statistics BUT with undocumented options i.e. ROWCOUNT and PAGECOUNT

 UPDATE STATISTICS Production.Product WITH ROWCOUNT = 10000000, PAGECOUNT = 1000000

Execute our first SELECT query but withDBCC FREEPROCCACHE” and it will show a different execution plan, as SQL Server Optimizer thought now number of rows are 10000000.
Now we have a better picture, that what will be the execution plan if number of rows are increased to 10000000 and it will be helpful to place new indexes and to take deceision like applying partition scheme.

To restore, actual number of rows, just rebuild all indexes on ‘Production.Product’ table.
ALTER INDEX ALLON Production.ProductDescription REBUILD

Note: Don’t use this method on production server.

SQL Server: Object Validation Error by Estimated Plan While Actual Plan Working Fine

It happened dozen of times when I tried to get an Estimated Execution Plan, it return object verification error, while the same Stored Procedure was working perfectly. Then what’s wrong with Estimated Execution Plan, lets create a simple procedure.


      IF 1 =2
            SELECT * FROM NoTable — NoTable doesn’t exists

On compilation and execution, above stored procedure will not return any error, because condition is never true, so it never need to exec SELECT * FROM NoTable, and to check that table exists or not. Now just try to get execution plan of


It will return error.

Msg 208, Level 16, State 1, Procedure Proc_TestProcedure, Line 8

Invalid object name ‘NoTable’.

Why So.

Because on estimation, query optimizer check each and every statement separately and once it try to estimate cost for “NoTable”, which never exists, it returns above mentioned error.

SQL Server: Restore Failure from Enterprise to Standard Edition

Yes you can restore a backup file which is taken from SQL Server Enterprise Edition to Standard Edition BUT if source database is not using Enterprise Edition Specific Features.  Like partition functions in our case.
This happened to one of my junior, when he was restoring a database (on SQL Server 2008 R2 Standard Edition) from full backup, which was taken from SQL Server 2008 R2 Enterprise edition. 6 GB database backup file took more than half an hour to restore and after showing 100 percent completion, on starting database it returned FAILURE ERROR.

Database ‘SQL2008R2_Ent_PartitionFunction’ cannot be started in this edition of SQL Server because it contains a partition function ‘SSF_PF_Right’. Only Enterprise edition of SQL Server supports partitioning.
Database ‘SQL2008R2_Ent_PartitionFunction’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 905)

Basically restore process is completed successfully and you can see database name in list and its files on required location but in inaccessible mode.

I think Microsoft should improve this process and these prerequisites should be checked first.

SQL Server : Query Result Showing Incomplete Text

Sometime, simple and basic problem can trap experts. This happened last week with one of my senior, who was trying to generate some scripts and saving output scripts to an output text file. On execution of these scripts he found that for some queries text was not complete. He tried to get result in text format but problem was same.  
File output
If you are facing same basic problem of SQL Server Management Studio then no need to worry, as you just need to make some changes in SSMS options.
Go to TOOLS — > OPTIONS — > QUERY RESULTS — > Result to Text — > Maximum number of characters displayed in each column
Default value is 256, which is too less, update it to your desired length. You can extend it to 8192 characters maximum.

SQL Server: Restore Failed, Logical file ‘xxxx’ is not part of database ‘xxxx’.

Problem occurs when we try to restore a database from backup set, but using MOVE option and when you provide wrong logical name of file in MOVE section of RESTORE script.
To avoid this problem, first, one must verify original logical names of files by using following  RESTORE FILELISTONLY .

Now if  somehow I use wrong logical file name, it will return error.

Correct the logical names, as per RESTORE FILELISTONLY output and it will work fine.

FROM  DISK = N’D:\temp.bak’ WITH 
 MOVE ‘TraceDB’ TO ‘d:\TraceDB2’,
  MOVE ‘TraceDB_logw’ TO ‘d:\TraceDB2_log’,

SQL Server: Script to Fix ALLOW_PAGE_LOCKS Option for All indexes on All Databases

Recently we have found that our index defragmentationjob is failing on a production server, due to REORGANIZE   failure of one of our index. SQL Server was unable to REORGANIZE this index because mistakenly we have an index with the ALLOW_PAGE_LOCKS options set to OFF
What is Page Lock Option?
According to BOL, If ALLOW_PAGE_LOCKS option is set to ON, it means Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.

If ALLOW_PAGE_LOCKS  option is set to off following query will return an error.
ALTER INDEXIX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
ON Person.Address REORGANIZE

The index “IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode” (partition 1) on table “Address” cannot be reorganized because page level locking is disabled.

You can correct it by simply updating ALLOW_PAGE_LOCKS option to ON with the help of following query

ALTER INDEXIX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
ON  Person.Address

To make sure to avoid this problem in futur for all datbases on a instance, Amna Asif has suggested following simple but efficient script to detect indexes with ALLOW_PAGE_LOCKS option set to OFF and automatically fix this problem for all of your databases on an instance.


Script By: Amna Asif
Purpose : To fix ALLOW_PAGE_LOCKS option on
                 all indexes of all databases on a particular instance


DECLARE@IndexCount int
DECLARE@UpdateIndexQuery Varchar(500)
— Table variable to hold intermediate result set
            DECLARE @IndexsInfo TABLE
            RowNo int identity(1,1),
            DatabaseName varchar(100),
            TableName varchar(100),
            IndexName varchar(100)
— Cursor to work on each changeable index of each db on an instance
      DECLARE DatabaseList CURSOR 
                    SELECT Name
                    FROM sys.databases
                    WHERE state_desc = ‘ONLINE’
                    AND is_read_only = 0
                    ORDER BY name
      OPEN DatabaseList
             FETCH NEXT FROM DatabaseList INTO@DBName
             WHILE @@FETCH_STATUS= 0
               INSERT INTO@IndexsInfo (DatabaseName,TableName,IndexName)
               EXEC(   SELECT ”’+@DBName+”’ AS DatabaseName,TABL.NAME AS TableName,indx.NAME AS IndexName     
                              FROM ‘+@DBName+‘.SYS.INDEXES indx
                              LEFT OUTER JOIN ‘+@DBName+‘.SYS.TABLES TABL ON indx.[OBJECT_ID]=TABL.[OBJECT_ID]
                              WHERE ALLOW_PAGE_LOCKS = 0           — where page lock option is not selected
                              AND indx.name NOT LIKE ”QUEUE%”  — we need only user defined indices
            FETCH NEXT FROMDatabaseList INTO @DBName
            CLOSE DatabaseList
            DEALLOCATE DatabaseList
—–Update allow_page_locks option for those indexes where we need 
 SET@IndexCount=(SELECTMAX(RowNo) FROM @IndexsInfo )
      WHILE @IndexCount >0  
          SET @UpdateIndexQuery=(SELECT ‘ ALTER INDEX ‘+ IndexsInfo.IndexName +‘ ON [‘+
            SET (
                  ALLOW_PAGE_LOCKS = ON
                  ) ; ‘
            FROM @IndexsInfo AS IndexsInfo
            WHERE IndexsInfo.RowNo=@IndexCount)
           SET @IndexCount=@IndexCount1

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)