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
    (
      RowID INTIDENTITY
                PRIMARYKEY,
      dbName SYSNAME,
      Totallogspace DEC(20, 2),
      UsedLogSpace DEC(20, 2),
      Status CHAR(1)
    )
   
 INSERT#LogSpaceStats
        ( dbName, Totallogspace,UsedLogSpace, Status)
        EXEC ( ‘DBCC sqlperf(logspace) WITH NO_INFOMSGS’
            )
    
–Get Info of All Drives
 DECLARE@ServerDrives TABLE
    (
      RowID intIDENTITY
                PRIMARYKEY,
      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
    (
      RowID INTIDENTITY
                PRIMARYKEY,
      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
 DECLAREcur_dbName CURSOR
    FOR SELECT  NAME
        FROM    SYS.DATABASES
        WHERE   state_desc =‘ONLINE’
                ANDis_read_only = 0
 OPENcur_dbName
 FETCH NEXT FROM cur_dbName into @dbName
 WHILE @@FETCH_Status = 0
    BEGIN
        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
                         FROM
                         (
                          SELECT Database_Name = ”’ + @dbName
                +”’
, Database_DSize = ltrim(str((convert (dec (15,2),’
       +@dbsize
       + ‘))* 8192 / 1048576,15,2) + ” MB”)
, ”Allocated_Space”=ltrim(str((CASE WHEN ‘
       +@dbsize + ‘ >= ‘+ @reservedpages
       + ‘
THEN convert (DEC (15,2),’
                +@reservedpages
                +‘)* 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),’
                +@logsize
                + ‘))* 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
    END
 CLOSEcur_dbName
 DEALLOCATEcur_dbName

 UPDATE@ServerDrives

 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’, ”)))
                                                sumofdrivespcae,
                                fss.DataFileDrive ASDRIVE
                      FROM      #ServerFileStats fss
                      GROUPBY  fss.DataFileDrive
                      UNION
                      SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FLogFileGrowth, ‘ MB’, ”)))
                                                sumofdrivespcae,
                                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’),
                      ELEMENTS)


— 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>’,”)

 DECLARE @flag BIT

 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>’ ;
 ELSE
    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’),
                       Elements)

 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

SELECT * FROM MyTable LIMIT 11, 5

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)

SELECT  *

      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
        ROWS FETCH NEXT 5 ROWS ONLY;

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.

USEAdventureWorks

GO
CREATE TABLE TempTable
      (tempID UNIQUEIDENTIFIER,tempMonth INT,tempDateTime DATETIME )
GO

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)
INCLUDE ( [tempID]) WITH ( ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
Now let’s execute a simple query with hard coded values in WHERE clause

SET STATISTICS IO ON

GO
SELECT * FROM TempTable
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 ??

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

SELECT * FROM TempTable
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:

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

SELECT * FROM TempTable
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

DROP TABLETempTable

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,
        pd.Description
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.ProductModel’),
object_id(‘Production.ProductModelProductDescriptionCulture’) ,
object_id(‘Production.ProductDescription’) ,
object_id(‘Production.Product’))        
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 with “DBCC 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: 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.

RESTORE DATABASE [TraceDB2]
FROM  DISK = N’D:\temp.bak’ WITH 
 MOVE ‘TraceDB’ TO ‘d:\TraceDB2’,
  MOVE ‘TraceDB_logw’ TO ‘d:\TraceDB2_log’,
  FILE = 4,  NOUNLOAD,  REPLACE,  STATS = 10

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
SET (ALLOW_PAGE_LOCKS = ON);

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
***********************************/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

DECLARE @DBName NVARCHAR(255)

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 
                  FOR
                    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
             BEGIN   
               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
             END
            CLOSE DatabaseList
            DEALLOCATE DatabaseList
—–Update allow_page_locks option for those indexes where we need 
 SET@IndexCount=(SELECTMAX(RowNo) FROM @IndexsInfo )
      WHILE @IndexCount >0  
      BEGIN
          SET @UpdateIndexQuery=(SELECT ‘ ALTER INDEX ‘+ IndexsInfo.IndexName +‘ ON [‘+
            IndexsInfo.DatabaseName+‘].dbo.[‘+IndexsInfo.TableName+‘]
            SET (
                  ALLOW_PAGE_LOCKS = ON
                  ) ; ‘
            FROM @IndexsInfo AS IndexsInfo
            WHERE IndexsInfo.RowNo=@IndexCount)
     
            EXEC(@UpdateIndexQuery)
        
           SET @IndexCount=@IndexCount–1
      END

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.