New answers tagged

2

I believe the problem is that you are executing all 5000 update statements as a single transaction. You need to break them up so each one is done in its own transaction. Then there are two options: If you are in simple recovery mode (or can temporarily switch the database to simple recovery mode), then breaking them up should be enough for the database to ...


4

I had the exact same situation and I created a SQL Agent job with the following script as the only step to monitor a job that occasionally runs long. if exists( SELECT * FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL AND ...


2

Write a job that will run every 5 minutes using sys.dm_exec_requests and send you an email for anything with total_elapsed_time over 15 minutes. I would recommend that everyone who has an OLTP environment, writes a job to monitor long running queries. It's much better to be proactive than to be reactive when it comes to long running SQL.


0

This feature is already built into SQL Server. I had a similar situation and what I did was to change my code to use a custom error number, above 50000, and set up a new Alert, under SQL Server Agent|Alerts in Management Studio, with that custom error number. Under the Options section, there is a Delay between responses section that can be used to mitigate ...


1

High Level overview of what you can do.. 1.Turn off email notifications for the job 2.Log every failure to table 3.Run one more job at your desired frequency which shows summarized data of the job status One more Approach would work,if your mail subject contains some thing to be identified as unique like 'Job test Failed' or 'job test succeded'.. Then ...


2

I don't think there's an option available to limit the number of times that a job can report failure. What comes to mind is to handle the notification yourself. Don't have SQL Server send an email on job failure, make that email part of your job script. You can log the email activity and control when your job sends them. It's pretty easy if your job is a ...


0

So this is what I came up with as a workaround if the intention is that multiple people in your organization should be notified if a job fails and a different group of multiple people for successes. You will notice the Steps 1-3 are the normal tasks that the schedule job is uses for as you would do for your task. There can as many steps as needed before ...



Top 50 recent answers are included