SQL Server: Client IP Along with DDL Change Log Using Service Broker

Last day, we have discussed all three possible methods for DDL Change Log and as per my suggestions, if you don’t need to conditionally allow/disallow changes then Service Broker is the best way to capture these changes. This method additionally allows you to submit change information to a separate instance on internet as a loosely coupled message.

One of blog reader raised a question that what else we need to add in script if we also need to capture machine IP from where change is coming.

Well answer is simple. We already have information of SPID so we can use this SPID and get client machine IP address by querying sys.dm_exec_connections.  

Change already defined stored procedure as following.

SQL Server: Three Common DDL Change Log Methods

Who is changing your objects (tables, views, stored procedures, functions etc) or creating new one, or who actually deleted one or more objects? These are normal questions when more than one person are working on a same database.

Production environment is mostly kept secure for unauthorized access and few known persons are allowed to make changes BUT still you need to keep a track of these changes and if it’s a development database then it is also must to keep a complete log of each change.

Three major ways, we can keep track of these changes.

1.                 DDL Trigger & Event Notifications

2.                 Extended Events

3.                 Service Broker & Event Notifications

DDL Trigger method is most commonly used method, where we write a ddl (after) trigger on each database separately and using information from event notifications, we decide whether to rollback any DDL change or just dump change information to a table.

How to create a DDL Trigger?

Extended Events, is the most advance method, not only for DDL change tracking but it’s going to be next biggest tool for DBAs. SQL Server 2012, introduced three new events for DDL change tracking.

1.                 object_altered

2.                 object_created

3.                 object_deleted

Paul Randal script for extended event creation is good one to follow, but don’t forget to change events.

 Service Broker (with event notifications), is the best way I have ever found for DDL Change Tracking before SQL Server 2012. Though its initial steps are bit lengthy, that is why; most people avoid using this method.

Using service broker, you can dump all databases changes data to a single table on an instance, or you can transmit changes information as a message to other instance on internet (if need to create a single point of administration for multiple instances).

(What is Service Broker and what type of objects you need to create, can be found here and here)

Use following simple steps to create DDL Changes Log, for multiple databases on an instance.

SQL Server: Placing Alert for Compatibility Level Change in SQL 2005

Microsoft SQL Server allows its users to keep behavior of a database compatible to its older versions. Like, if someone is using “*=” type of left outer joins in some quires/Stored Procedures as she created it for SQL Server 2000. Though such join are not allowed in SQL Server 2005 and subsequent versions but one still can keep database behavior as SQL Server 2000 by keeping its compatibility level to 80.

Recently, a client reported that someone (DBA or Application) is changing his database compatibility, which should remain compatible to SQL Server 2000 (compatibility level 80). He wants to know at what time this change is being made.

SQL Server 2008 and subsequent versions keep record of this compatibility change to its log, but SQL Server 2005 has no such facility. It means, in SQL Server 2005, you never know when someone has changed compatibility level.

In SQL Server 2008 and subsequent versions one can change compatibility level of a database by following simple TSql statement.

ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 90;

But in SQL Server 2005, only method to change this compatability level is its system stored procedure i.e. sys.sp_dbcmptlevel. SQL Profiler is the only place where you can trace when this stored proecdure was executed. But what if, we need to place an alert for this change and generate a mail for this change. Or what if, we need to stop users/applications to change a database compatability level.

Only way to achieve this functionality is,  to update system stored procedure  sp_dbcmptlevel.

Lets perform this task, step by step.

Step 1:  Stop SQL Server 2005 services

Step 2:  Login using DAC (Dadicated Administrative Connection). For this right click on SQL Server 2005 service, on Advanced tab, change startup parameters by adding -m; at existing values.

Step 3: Start SQL Server 2005 services

Step 4: Open SQL Server Management Studio and open Database Engine Query

Step 5: Login as valid sysadmin user or ADMIN:InstanceName

Step 6: Change mssqlsystemresource database to read_write mode

Step 7: It’s the time to update our system stored procedure i.e. sp_dbcmptlevel. If you need to keep only comptability level to 80 or 90 then change following lines of stored procedures with same values i.e.80 or 90 or as per your choice.

select  @cmptlvl60 = 60, 
@cmptlvl60 = 65,
@cmptlvl60 = 70,
@cmptlvl60 = 80,
@cmptlvl60 = 90, 

And if you also need to add a mail alert for this change then add following code in error control portion of stored procedure.

DECLARE @bodyText VARCHAR(200)

SET @bodyText='User '
+ CONVERT(VARCHAR,SYSTEM_USER)  
+' trying to change Compatibility Level of Database '                            + CONVERT(VARCHAR,@dbname)
+ ' at '
 + CAST(GETDATE() AS VARCHAR(50)) 
EXEC msdb.dbo.sp_send_dbmail @recipients='essmess@gmail.com;', --Change Email Address Accordingly 
@subject = 'Compatibility Level Change Alter', 
@profile_name = 'DBTeam', --Change DB mail Profile Accordingly 
@body = @bodyText, 
@body_format = 'TEXT' ;

Here is complete updated script of stored procedure. (This script is only applicable to SQL Server 2005, for SQL Server 2008 and subsequent version, its totally different, which you can get by sp_helptext)

Step 8: Change mssqlsystemresource database to read_only mode

Step 9: Close SSMS session, stop SQL Server services and change its startup parameters back to normal.

Step 10: Start SQL Server Services and you are done.

SQL Server: Why a Session With sp_readrequest Takes so Long to Execute

While applying, Long Running Sessions Detection Job on a production server, we start receiving alert that a session is taking more then 3 minutes. But what actually this session was doing. Here is the alert report.

SP ID	Stored Procedure Call	DB Name	Executing Since
58	msdb.dbo.sp_readrequest;1�	msdb	3 min

sp_readrequest is a system stored procedure, which basically reads a message request from the the queue and returns its  contents.

This process can remain active for a time we have configured for parameter DatabaseMailExeMinimumLifeTime, at the time of database mail profile configuration. 600 seconds is the default value for this external mail process. According to BOL DatabaseMailExeMinimumLifeTime is the The minimum amount of time, in seconds, that the external mail process remains active.

This can be changed, at the time of mail profile configuration or you can just use update query to change this time.

UPDATE msdb.dbo.sysmail_configuration

SET paramvalue = 60 --60 Seconds

WHERE paramname = 'DatabaseMailExeMinimumLifeTime'

We have changed this to 60 seconds to resolve our problem.

SQL Server: Template Explorer, A Developer’s Close Friend

How many of us really memorize all create, update or drop/delete scripts. Very honestly, I just remember Create Procedure and Create Function scripts. But, reality is that, we need not to remember all these codes/scripts, especially when Template Explorer is there for our help.

If you can’t see Template Explorer  in your SQL Server Management Studio, then just move your mouse pointer to top menu, view , Template Explorer or if you are short keys fan then just press Ctrl+Alt+T from your keyboard.

See how quickly I can create a new trigger.

 

SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem

Recently I have received a mail from one of blog reader, who explained his problem as following:

“I have tried scrip to create logon trigger from your blog post “Restrict Login from Valid Machine IPs Only (Using Logon Trigger)” BUT problem is that, I forgot to put localhost in my safe list, and now I am unable to login to my instance.”

Well, if same happened to you, then you need to login using Dedicated Administrator Connection. What is DAC and how to you use it Read This.
DAC can be established using sqlcmd or through SSMS. On command prompt, type this to establish connection.

Sqlcmd –S localhost –d master –A

You can provide instance name instead of localhost. Next thing is to disable our logon trigger, using following command.

DISABLE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

Where “tr_LogOn_CheckIP” is the name of our logon trigger. On next line type GO to execute DISABLE command.

Now you can login to your database server. Once login, check out trigger is disabled.

You can achieve all this through SQL Server Management Studio. To establishing dedicated connection, click on  FILE----NEW----Database Engine Query

Login through valid SYSADMIN user, by providing server name with extra word and a colon, i.e. Admin:

In query window, type same tsql and execute to disable trigger.

And never forget to add your server IP or <localhost> in safe list, while creating logon trigger.

SQL Server: Keeping Log/Alert for Job Disable/Enable Status

To monitor production database servers, Database Administrators create different jobs and depends upon these jobs to work for them i.e. to check if server have enough space, database is not corrupt, queries are not running slow, index defragmentation and many more. BUT what if somehow, someone accidently disabled a job and forgot to enable it back. No alert will be created as job is disabled. Or it can be fatal when you need to restore a database and found that backup job was not working as it was disabled by someone ;)

Is there any way to get alert if someone changes any job status on production server?

YES, by creating following trigger on msdb.dbo.sysjobs can resolve this problem. It will detect any change in job status and will mail a message like following to your DBA team.

Job "Daily Full Backup" is recently DISABLED by user aasim.abdullah with session id 167 and host name IdeaWrox-DB01 at Sep 12 2012 4:00:03:673AM

 

 

SQL Server: Automatically Detect and Kill Long Running Sessions on Production Servers

Problem:  On production servers, how to detect long running sessions and kill them automatically if they are exceeding a specific amount of time.

Solution:

Solution to this problem is very simple. Just create a job, which will detect long running sessions by running query against sys.sysprocesses executing SP_WHO or sp_WHO2 and then kill those sessions which are exceeding a time limit. BUT major problem is that SP_WHO or SP_WHO2 are unable to provide important information, like actually which execution command, stored procedure call or tsql batch is being executed by this culprit session? To avoid, in future and to find out permanent solution for such long running quires we need to mail them before we kill these sessions.

Following is the script we like to use on production servers, to find out costly sessions and send complete information to DBA team through mail, before killing these costly processes, automatically.

Mail output would be as following.

SQL Server: Script to Start and Stop a New Trace without Using Profiler GUI

SQL Server Profiler is an useful tool, but basically, important part of profiler is trace which this graphical interface shows. Trace data can be collected and saved with in a file, even without using SQL Server Profiler graphical interface.

To do so you need a script that will perform this task. Good thing is that, SQL Server Profiler helps us to create this script, through following path.

Now, only you need to change it according to your requirements. Here is a complete script for auto trace which we use on production servers, to capture quires taking time more than 5 seconds to execute. Script can be used in a job to execute on daily bases. 

 

To stop this auto trace, you just need to change its status to STOP and then CLOSE. Keep in mind to close a trace, you must stop it first.

SQL Server: Restrict Login from Valid Machine IPs Only (Using Logon Trigger)

Today, I have practically learned how to stop valid database users to login on SQL Server instance from invalid machines (IPs).

Process is very simple. Just create a Logon Trigger and check if login user is coming from valid IP or not. If not, then just kick him out.

Download Script

USE master

GO

-- Create table to hold valid IP values

CREATE TABLE ValidIPAddress (IP NVARCHAR(15)

CONSTRAINT PK_ValidAddress PRIMARY KEY)

-- Declare local machine as valid one

INSERT INTO ValidIPAddress

SELECT '<local machine>'

-- Create Logon Trigger to stop logins from invalid IPs

CREATE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

    FOR LOGON

AS

    BEGIN

        DECLARE @IPAddress NVARCHAR(50) ;

        SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',

                                           'NVARCHAR(50)') ;

        IF NOT EXISTS ( SELECT  IP

                        FROM    master..ValidIPAddress

                        WHERE   IP = @IPAddress )

            BEGIN

            -- If login is not a valid one, then undo login process

                SELECT  @IPAddress

                ROLLBACK --Undo login process

            END

    END

Once trigger is created, you can find it under Server Objects -- > Triggers tab

From invalid IP, which you have not added in secure list will see following error on log-in attempt.

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 quickly databases on these instances are growing and does target instance has required 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, required and currently available on hard drives.

Download Script
 /*

 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 INT IDENTITY
                PRIMARY KEY,
      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 int IDENTITY
                PRIMARY KEY,
      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 INT IDENTITY
                PRIMARY KEY,
      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
 DECLARE cur_dbName CURSOR
    FOR SELECT  NAME
        FROM    SYS.DATABASES
        WHERE   state_desc = 'ONLINE'
                AND is_read_only = 0
 OPEN cur_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((CAST(('
                + @reservedpages + ' - ' + @usedpages
                + ')AS BIGINT) * 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
 CLOSE cur_dbName
 DEALLOCATE cur_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 AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUP BY  fss.DataFileDrive
                      UNION
                      SELECT    SUM(CONVERT(DEC(20, 2), REPLACE(fss.FLogFileGrowth, ' MB', '')))
                                                sumofdrivespcae,
                                fss.LogFileDrive AS DRIVE
                      FROM      #ServerFileStats fss
                      GROUP BY  fss.LogFileDrive ) SumDrive
          GROUP BY  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
                  ORDER BY  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

DF = Data Files
LF = LogFiles