January 19, 2011 at 11:37 pm
hey,
i am trying to create a new job daily for mailing all my disk spaces and free spaces to outllok.so i dont know how to create and where to create
eg:i want to get the mail like below format
MySystemName DiskDrive Total Space Free Space
XXXX C: 10GB 4GB
YYYY D: 20 GB 16GB
CAN ANY ONE HELP ME
January 19, 2011 at 11:49 pm
You will need to perform the following:
1. Create a Database Mail Profile(Not applicable in SQL Server 2005 Express Edition). The steps for doing it can be obtained on the below link:
http://www.mssqltips.com/tip.asp?tip=1100
2. Create a stored procedure which contains the required logic to compute the free space as well as used space for a particular disk. You can use xp_fixeddrives to accomplish this task.
Satnam
January 19, 2011 at 11:59 pm
Once the logic is designed then u will need to create a job which will execute the required stored procedure to send the email. The schedule of the job depends upon your requirements.
Satnam
January 20, 2011 at 12:04 am
Hi Singh,
Thanks for the reply.i knew the database mail profile.but i want the logic of code.
i knew the job also.already i wrote code for this but this code will not executing the required output.
January 20, 2011 at 12:10 am
Can u let me know the code?
Satnam
January 20, 2011 at 12:14 am
CREATE PROCEDURE usp_MyDiskFreeSpace
@DriveCBenchmark int = 1024,
@OtherDataDriveBenchmark int = 2048
AS
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #diskfreespace
(
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)
DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
INSERT INTO #disk_free_space
EXEC master..xp_fixeddrives
SELECT @DiskFreeSpace = FreeMB FROM #disk_free_space where DriveLetter = 'C'
IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' + CAST(@DiskFreeSpace AS VARCHAR) + ' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End
DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #diskfreespace where DriveLetter not in ('C')
open DriveSpace
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
WHILE (@@FETCH_STATUS = 0)
Begin
if @DiskFreeSpace < @OtherDataDriveBenchmark
Begin
set @MailSubject = 'Drive ' + @DriveLetter + ' free space is low on ' + @@SERVERNAME
set @AlertMessage = @DriveLetter + ' has only ' + cast(@DiskFreeSpace as varchar) + ' MB left. Please increase free space for this drive immediately to avoid production issues'
-- Send out email
EXEC master..xp_sendmail @recipients = 'xxxxx',
@subject = @MailSubject,
@message = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace
deallocate DriveSpace
DROP TABLE #diskfreespace
GO
January 20, 2011 at 12:19 am
Also, can u let me know the error message and how many disks are there on the server for which u need such alerts?
Satnam
January 20, 2011 at 2:45 am
the problem is yet to be not solved
January 20, 2011 at 2:52 am
I have developed a similar sort of script around few weeks back. I wrote it using HTML along with SQL. If u can hold on for some time then i shall be able to send u those scripts.
Cheers,
Satnam
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply