December 6, 2011 at 9:05 pm
Dear antohny
i recive the notifications,but it was:
JOB RUN:'Check Disk Space' was run on 12/7/2011 at 1:00:00 AM
DURATION:0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by Schedule 38 (Check Disk Space). The last step to run was step 1 (Check Disk Space).
------------------
Date12/7/2011 1:00:00 AM
LogJob History (Check Disk Space)
Step ID1
ServerSQLDBCLU
Job NameCheck Disk Space
Step NameCheck Disk Space
Duration00:00:00
Sql Severity16
Sql Message ID17938
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: NT AUTHORITY\SYSTEM. SQL Mail does not work with the 64-bit version of SQL Server [SQLSTATE 42000] (Error 17938). The step failed.
------------------------------------------------
how can i fix that?
December 6, 2011 at 9:51 pm
Message
Executed as user: NT AUTHORITY\SYSTEM. SQL Mail does not work with the 64-bit version of SQL Server [SQLSTATE 42000] (Error 17938). The step failed.
------------------------------------------------
how can i fix that?
This is the time you confgure Database Mail for your SQL Server instead of SQL Mail. Configuring Database Mail & using it is very easy as well as simple procedure.
As anthony has asked this many times, why not use the new feature when you have got SQL Server 2005 in hand :cool:?
To understand how to configure Database Mail click here[/url].
To understand how to use SP_Send_DBMail stored procedure click here [/url].
December 7, 2011 at 1:41 am
Right, going back to a post I said earlier.
You say you are running SQL Mail on this server. This means that you must have a linked server to which you pass all mail requests which is a 32 bit server. So find which procedures send mail via SQL Mail, run the below SQL in all of your user databases.
SELECT object_name(id) FROM sys.syscomments WHERE [text] LIKE '%xp_sendmail%'
If the above returns rows, then look at the definition of the objects and see where it calls xp_sendmail so that you can copy it to the drive notifications.
If the above does not return any rows in any databases when you do NOT use SQL Mail, you must use DB Mail so execute this in all your databases
SELECT object_name(id) FROM sys.syscomments WHERE [text] LIKE '%sp_send_dbmail%'
If the above does return rows, look at the object definition, look at the parameters passed into the procedure call, copy this into the notification procedures.
If both of the above do not return any rows, then you are not using either SQL Mail or DB Mail, so you will need to configure DB Mail, then configure a email notification based on the parameters which sp_send_dbmail requires to alert you via the procedures.
December 7, 2011 at 2:25 am
Dear sir
I configure DB mail and test it and its working fine, but when i run the job i recive the following error:
JOB RUN:'Check Disk Space' was run on 12/7/2011 at 11:55:45 AM
DURATION:0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by User sa. The last step to run was step 1 (Check Disk Space).
so do i need to delete the operators and re create them again?
Thanks & Regards
December 7, 2011 at 2:30 am
what is the actual error message of the job step, not the whole job
December 7, 2011 at 2:34 am
if you have the operators already set up on the job then it should work, as you have not change the way in which SQL agent sends its mail, just how that stored procedure sends the mail
December 7, 2011 at 3:01 pm
I assume you have turned on database mail in the surface area configuration (or through a script). Have you created a mail profile?
select [name] from msdb..sysmail_profile
----------------------------------------------------
December 8, 2011 at 12:13 am
Dear,
The better to setup this is to first configure the SQL Mail setup from SSMS which is wizard based GUI, and do test email to your email addresses, after you done with sucessfull testing fo the following
search a script to retrive system drive information from os using sql and email to your email ids using the above email profile that you created , create a storedprocedure in any of your database and do the final step also from GUI.
create a sql job using SSMS GUI by using agent and point that storedprocedure in that job
🙂
SQL-DBA,
Commercial Bank,
Asia.
December 8, 2011 at 1:44 am
Thanks yarizus for sumarizing the topic.
Just one thing, why did you detail SQL Mail, surely you mean DB Mail? This is SQL 2008 and SQL Mail is now a depreciated feature and should not be used for any developments using SQL 2005 or above.
The only reason SQL Mail exists in 2005 and 2008 is for backwards compatability, as the compatability level 80 is removed from Denail/2012 so will the use of SQL Mail, so everything using SQL Mail will need to be converted to DB Mail.
December 8, 2011 at 3:50 am
Dear all
I run:
select [name] from msdb..sysmail_profile
and the resault is:
Alerts DB Mail
SQL Mail
December 8, 2011 at 4:40 am
now it is starting to make more sence,
it may be that we are confusing SQL Mail with SQL Mail, as SQL Mail(xp_sendmail) and SQL Mail as the profile of DB Mail.
it would seem that your not actually using xp_sendmail your using sp_send_dbmail.
look back up the threads for the syscomments and find an object which has sp_send_dbmail inside it, copy the sp_send_dbmail line and replace it for xp_sendmail in the notification procedure if you already haven't
as for the operators, ensure that the job has operators set and that you can send an email via sp_notify_operator
December 8, 2011 at 4:30 pm
I think you need to spend a little time ensuring you have set up the mail profile and account correctly. You were given a couple of links earlier for setting up db mail. Please look through them if you haven't already. If the calling credentials are denied by the proc sp_send_dbmail, (sorry if this gets too hairy) you can create a wrapper function that itself accepts the parameters and calls sp_send_dbmail. You can configure the permissions on that proc/wrapper to accomodate the credentials that get passed.
Mark
----------------------------------------------------
December 10, 2011 at 11:18 pm
Dear all
This is the Stored procedure which i am useing:
------------------------------------------------
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DiskFreeSpaceAlert]
@DriveCBenchmark int = 1024,
@OtherDataDriveBenchmark int = 2048
AS
--By: This procedure sends out an alert message when hard disk space is below a predefined value. This procedure can be scheduled to run daily so that DBA can act quickly to address this issue.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)
DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
/* Populate #disk_free_space with data */
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 = 'a.ali@arkan.ae',
@subject = @MailSubject,
@message = @AlertMessage
End
DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #disk_free_space 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 = 'a.ali@arkan.ae',
@subject = @MailSubject,
@message = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace
deallocate DriveSpace
DROP TABLE #disk_free_space
December 10, 2011 at 11:32 pm
when i try to replace:
xp_sendmail
with
sp_send_dbmail
i got the following error:
The module 'usp_DiskFreeSpaceAlert1' depends on the missing object 'master..sp_send_dbmail'. The module will still be created; however, it cannot run successfully until the object exists.
December 11, 2011 at 1:34 am
that's because sp_send_dbmail exists in the msdb database, so you need to do msdb.dbo.sp_send_dbmail
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply