Monthly Archives: July 2012

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


 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
                          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 = ‘’,
    @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.