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 ....

 

About Me