June 6, 2017 at 6:23 am
Hello
I have created a job which send me an alert when D drive(dedicated drive for tempdb) free space is less than 2048 MB.
When I executed,exec xp_fixeddrives it shows that D drive has 57000MB(`57 GB out of 60GB) free space, but we are continuously getting alert from job. Can someone please suggest?
Job code :
declare @Free_MB int
create table #Free(
Drive char(1),
Free_MB int)
insert into #Free exec xp_fixeddrives
select @Free_MB = Free_MB from #Free where Drive = 'D'
if @Free_MB < 2048
exec msdb.dbo.sp_send_dbmail
@recipients = 'mailid.com' ,
@subject ='Space is critical on D Drive',
@body = 'Less than 2 GB available'
June 6, 2017 at 6:44 am
If you run the following, what do you get?DECLARE @Free_MB int;
CREATE TABLE #Free
(Drive char(1),
Free_MB int);
INSERT INTO #Free EXEC xp_fixeddrives;
SELECT Free_MB FROM #Free WHERE Drive = 'D';
DROP TABLE #Free;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 6, 2017 at 6:50 am
if that is a direct copy of your code, one thought could be that you haven't explicitly declared the end of your statements, this can always cause odd results?
I've run your code with correct syntax and it seems to be ok? what results are you getting from the xp and from your temp table?
One final thought, have you configured your job correctly? i.e. is the alert you are getting from the code or could you have "On success action: Quit the job reporting failure" configured? (I'm presuming you have alerts configured for failing jobs?)
what is the alert you are getting from the job?
June 6, 2017 at 6:56 am
Sreejith! - Tuesday, June 6, 2017 6:23 AMbut we are continuously getting alert from job. Can someone please suggest?
Well, yes, you would. You'll get it every time the job runs and finds that disk space is low. How often does the job run? You'll need to make it a little more sophisticated. Create a permanent database table with an AlertSent column. When an alert is triggered and sent, update AlertSent to Yes. If the job runs again and finds low space but AlertSent is Yes, don't send the alert. If the job subsequently runs and doesn't find low space, reset AlertSent to No.
John
June 6, 2017 at 7:05 am
Hi Thom
Thanks for replying
I have executed the code & it returned 57000MB.For some reason the query execution is entering into IF condition
Hi Dim,
Yes, There is no syntax error & job is configured correctly.
June 6, 2017 at 7:09 am
John Mitchell-245523 - Tuesday, June 6, 2017 6:56 AMSreejith! - Tuesday, June 6, 2017 6:23 AMbut we are continuously getting alert from job. Can someone please suggest?
Well, yes, you would. You'll get it every time the job runs and finds that disk space is low. How often does the job run? You'll need to make it a little more sophisticated. Create a permanent database table with an AlertSent column. When an alert is triggered and sent, update AlertSent to Yes. If the job runs again and finds low space but AlertSent is Yes, don't send the alert. If the job subsequently runs and doesn't find low space, reset AlertSent to No.
John
Email is triggered even we have enough disk space. It is supposed to trigger email when we have low disk space(less than 2GB). Job is scheduled to run every one hour
June 6, 2017 at 7:22 am
As a thought, I'm wondering if the SQL Agent is running into a permission problem (either unable to run xp_fixeddrives, or being denied access to the drives themselves)
Try modifying your job to store the results in a permanent table, let it run once, then see what's in the table. If it's empty, there's your problem. Then it becomes a case of tracking down where the permissions failure lies.
June 6, 2017 at 7:31 am
jasona.work - Tuesday, June 6, 2017 7:22 AMAs a thought, I'm wondering if the SQL Agent is running into a permission problem (either unable to run xp_fixeddrives, or being denied access to the drives themselves)Try modifying your job to store the results in a permanent table, let it run once, then see what's in the table. If it's empty, there's your problem. Then it becomes a case of tracking down where the permissions failure lies.
Service account has syadmin access on SQL Server
June 6, 2017 at 7:49 am
Sreejith! - Tuesday, June 6, 2017 7:31 AMjasona.work - Tuesday, June 6, 2017 7:22 AMAs a thought, I'm wondering if the SQL Agent is running into a permission problem (either unable to run xp_fixeddrives, or being denied access to the drives themselves)Try modifying your job to store the results in a permanent table, let it run once, then see what's in the table. If it's empty, there's your problem. Then it becomes a case of tracking down where the permissions failure lies.
Service account has syadmin access on SQL Server
I'd still suggest letting it run once saving to a table you can query afterwards. Verify that it's actually getting data to check against (after all, if for some reason when run as a job it's saving NULL for the drive free space, your alert will fire every single time.)
June 6, 2017 at 8:08 am
jasona.work - Tuesday, June 6, 2017 7:49 AMSreejith! - Tuesday, June 6, 2017 7:31 AMjasona.work - Tuesday, June 6, 2017 7:22 AMAs a thought, I'm wondering if the SQL Agent is running into a permission problem (either unable to run xp_fixeddrives, or being denied access to the drives themselves)Try modifying your job to store the results in a permanent table, let it run once, then see what's in the table. If it's empty, there's your problem. Then it becomes a case of tracking down where the permissions failure lies.
Service account has syadmin access on SQL Server
I'd still suggest letting it run once saving to a table you can query afterwards. Verify that it's actually getting data to check against (after all, if for some reason when run as a job it's saving NULL for the drive free space, your alert will fire every single time.)
Yes, and just because it's sysadmin, that doesn't automatically give it access to resources outside of SQL Server, such as the disk drives.
John
June 6, 2017 at 8:33 am
jasona.work - Tuesday, June 6, 2017 7:49 AMSreejith! - Tuesday, June 6, 2017 7:31 AMjasona.work - Tuesday, June 6, 2017 7:22 AMAs a thought, I'm wondering if the SQL Agent is running into a permission problem (either unable to run xp_fixeddrives, or being denied access to the drives themselves)Try modifying your job to store the results in a permanent table, let it run once, then see what's in the table. If it's empty, there's your problem. Then it becomes a case of tracking down where the permissions failure lies.
Service account has syadmin access on SQL Server
I'd still suggest letting it run once saving to a table you can query afterwards. Verify that it's actually getting data to check against (after all, if for some reason when run as a job it's saving NULL for the drive free space, your alert will fire every single time.)
Hmmm...I might be missing your actual intention, but if the variable IS NULL, the code in the IF block will NOT execute.
NULL < 2048 does not evaluate to TRUE.
The action point is still very worthwhile, of course (seeing what values are being returned), but I just wanted to clarify this bit.
Cheers!
June 6, 2017 at 8:55 am
jasona.work - Tuesday, June 6, 2017 7:49 AMI'd still suggest letting it run once saving to a table you can query afterwards. Verify that it's actually getting data to check against (after all, if for some reason when run as a job it's saving NULL for the drive free space, your alert will fire every single time.)
Curious, unless I'm missing something blindingly obvious (?) why would null fire the alert every time?
If null < 2048 would result in false and so shouldn't fire the command? worst case scenario a null exception should be thrown and the procedure fail?
June 6, 2017 at 9:01 am
Jacob Wilkins - Tuesday, June 6, 2017 8:33 AMHmmm...I might be missing your actual intention, but if the variable IS NULL, the code in the IF block will NOT execute.NULL < 2048 does not evaluate to TRUE.
The action point is still very worthwhile, of course (seeing what values are being returned), but I just wanted to clarify this bit.
Cheers!
DimPerson - Tuesday, June 6, 2017 8:55 AMCurious, unless I'm missing something blindingly obvious (?) why would null fire the alert every time?
If null < 2048 would result in false and so shouldn't fire the command? worst case scenario a null exception should be thrown and the procedure fail?
You are both correct, I was wrong. If it's reporting NULL it should just bail out and do nothing.
June 6, 2017 at 9:11 am
DimPerson - Tuesday, June 6, 2017 8:55 AMjasona.work - Tuesday, June 6, 2017 7:49 AMI'd still suggest letting it run once saving to a table you can query afterwards. Verify that it's actually getting data to check against (after all, if for some reason when run as a job it's saving NULL for the drive free space, your alert will fire every single time.)Curious, unless I'm missing something blindingly obvious (?) why would null fire the alert every time?
If null < 2048 would result in false and so shouldn't fire the command? worst case scenario a null exception should be thrown and the procedure fail?
I apologize in advance for being pedantic, but strictly speaking NULL < 2048 should evaluate to UNKNOWN. :rolleyes:
June 6, 2017 at 11:56 pm
I tried to save it to table & alert got triggered again. As I mentioned earlier, D drive is a dedicated drive for TEMPDB and in SSMS available free space is showing as negative values; which is due to issue mentioned in below blog post
https://blogs.msdn.microsoft.com/ialonso/2012/10/08/inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only/
Does it has something to do with xp_fixeddrives procedure???
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply