Microsoft SQL Server 2005/2008R2:Daily Send Mail for Disk Space Usage.



Read this document for getting knowledge of  Database Mail.

First need to create Database Mail Server.

2nd thing to Create Stored Procedure to Mail Disk Usage Space.

3rd thing to Schedule this procedure.

1.Create Database Mail Server.

Right Click on Management-->Database Mail






 Click on Add.






















Test Database Mail.



Check Mail For Confirmation.

2.Create Stored Procedure to DiskUsageMail.

USE [master]
GO
/******************************************************************************/   
/* Copyright: 2012   Database Experts
*    
* Name : [DiskUsageMail]$   
* Version : $Revision: 1    
* Last Updated : $Modtime:
* Created By : $Author: Bhavesh Thakkar
* Modified By : $Author:    
* Script : This procedure is used to Shrink Database transaction log.
* Input:
* Version Info:   
* No.                 Author                            Date                                                           Comments   
* --- ------ ---- --------   
    1                Bhavesh Thakkar                        2012-07-25 
*/
/*****************************************************************************/   


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[DiskUsageMail]

as

Begin

Declare @FreeSpace decimal(8,2),
        @Drive varchar(10),
        @Subject varchar(500),
        @to varchar(1000),
        @ProfileName varchar(100),
        @msg varchar(max),
        @sql varchar(2000),
        @body_format varchar(10)

CREATE TABLE #temp (Drive varchar(10),FreeSpace decimal(8,2))


insert into #temp EXEC master..xp_fixeddrives

DECLARE db_cursor CURSOR FOR

Select Drive,FreeSpace/1024 [FreeSpace_GB] from #temp

SET @msg='This is the information of Free Space in '+@@SERVERNAME+'. <br /><br /><Table border=1><tr><td colspan=2 align="center" bgcolor=grey>Disk Space Usage</td></tr>
          <tr><td>Drive</td><td>FreeSpace in GB</td></tr>'
        
SET @to='Database Experts@Database Experts.com'

SET @Subject='[Automated Mail @'+convert(varchar(19),GETDATE(),121)+']Disk Space Usage Details:'+@@SERVERNAME

SET @ProfileName='SQLMail'

SET @body_format='HTML'

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @Drive,@FreeSpace 

WHILE @@FETCH_STATUS = 0 

BEGIN

SET @msg=@msg+'<tr><td>'+@Drive+'</td><td>'+cast(@FreeSpace as varchar(100))+'</td></tr>'

FETCH NEXT FROM db_cursor INTO @Drive,@FreeSpace 

END

SET @msg=@msg+'</Table>'

SET @sql='USE msdb '+
'EXEC sp_send_dbmail @profile_name='''+@ProfileName+''',
@recipients='''+@to+''',
@subject='''+@Subject+''',
@body='''+@msg +''',
@body_format ='''+@body_format+''''

--print @sql

EXEC(@sql)

end

3.Schedule this procedure to get the information of Disk Usage Daily/Weekly/Monthly.

Right Click on SQL Server Agent-->New-->Job.











Got to Steps.

Click on New







 Click ok

Go to Schedule.

Click on New.




Add Value in Alerts,Notification or Target as per your requirement.


Click Ok to Complete.


Sample Mail ....

 

Comments

  1. Thanks a Ton!!

    It worked out with no efforts from my end.

    ReplyDelete
  2. The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2015-09-28T11:17:59). Exception Message: Could not connect to mail server. (No such host is known).
    ),472,14,,28-Sep-15 11:17:59 AM,sa

    ReplyDelete

Post a Comment

Dear User,

Thank you very much for your kind response

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.