Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Jobs issue Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 8:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 6, 2014 1:32 AM
Points: 81, Visits: 944
We have faced a scenario in which jobs are scheduled to run on daily basis,but one day what happened that the job didnt run and we didnt get any alert and as we work in a financial firm for which data is quite essential we suffered a heavy loss due to that job not running.

Is there any way through which we can track that job didn't run due to any reason.As it is virtually not possible to go on each server and check each job has run or not for that particular day as there are many servers and lots of jobs in that.
Urgent help is required.
Post #1357770
Posted Tuesday, September 11, 2012 9:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 18,060, Visits: 16,090
You can set an alert to text or email you.

You can also create a job that checks for any failed jobs and then emails job results to you.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1357773
Posted Tuesday, September 11, 2012 10:35 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
mahesh.dasoni (9/11/2012)
We have faced a scenario in which jobs are scheduled to run on daily basis,but one day what happened that the job didnt run and we didnt get any alert and as we work in a financial firm for which data is quite essential we suffered a heavy loss due to that job not running.

Is there any way through which we can track that job didn't run due to any reason.As it is virtually not possible to go on each server and check each job has run or not for that particular day as there are many servers and lots of jobs in that.
Urgent help is required.


When you say the job didn't run, do you mean it never executed, or that it failed? If it is the former I would also be concerned to find out why the job didn't run and treat the root cause.

In my environment I have a PS script that runs across my whole environment each morning picking up job failures by interrogating the sysjobhistory table on each server. It drops the results into a table on a management server and I have an SSRS report that I check each day.



Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1357778
Posted Tuesday, September 11, 2012 10:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 6, 2014 1:32 AM
Points: 81, Visits: 944
We have alert set virtually for everything but that day it didn't run due to sql server agent issue.

The error we got on sql server agent log was :-
SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.


Is there any way through which we can check that particular job in a schedule has not run.
Post #1357783
Posted Tuesday, September 11, 2012 10:58 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
You can query the sysjobactivity table in msdb to get various information regarding execution dates and times. The trouble with a job that doesn't run is that you won't find a log anywhere of it actually failing to execute so you will need to infer this from the sysjobxxx tables.

And do investigate the error you posted up further.


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1357785
Posted Friday, September 14, 2012 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:04 AM
Points: 18, Visits: 480
I have a PS script that interrogates JobServer.Jobs on all servers across my estate at 7am.
So when I come into work it has emailed me a single email for each server if jobs have failed with the server name and the name of the job that has failed.

The same PS script also fills an Excel sheet and colour codes the results RAG for Failed Unknown Success which I check every day


http://sqldbawithabeard.com Its where I blog

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1359241
Posted Wednesday, September 19, 2012 3:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
mrrobsewell (9/14/2012)
I have a PS script that interrogates JobServer.Jobs on all servers across my estate at 7am.
So when I come into work it has emailed me a single email for each server if jobs have failed with the server name and the name of the job that has failed.

The same PS script also fills an Excel sheet and colour codes the results RAG for Failed Unknown Success which I check every day

Nice! Care to share? If you don't mind post the script to this thread or contribute to the site by clicking the Write For Us link on the left nav.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1361634
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse