SQL Agent Jobs not running - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Agent Jobs not running

Author

Topic

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-13 : 12:16:53

Hey Everyone!

I am working with SQL 2008 and seem to be having issues with running some jobs that create views and functions.

We use a replication database that is replicated at 5am every morning. Then around 6:30 I have some jobs to run (custom functions and views for my reports). The problem is they don't seem to be created in the database even though the job activity will say "successful".

I double checked the database it's suppose to use and it is correct.
I double checked the jobs to make sure they are enabled.
I double checked the schedule to make sure they run in the correct sequence.

I'm a loss as to what to look for next.

The jobs I created on an SQL sever 2005 seem to run just fine with no problems.

Is there a step I'm missing?

Any help would be great!

Larry

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-13 : 12:22:52

How do the jobs create these views and functions?

If they run a T-SQL script, it is likely that it is erroring out, but not causing the job to fail if there are GO seperators between statements.

What do you see in the job history of the job(s)

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-13 : 12:36:35

They are running off the replicated data tables. I only have 2 functions that get created first, the the views run. It's two separate jobs.

in the job history it says they are created and ran successfully. Occasionally I'll see that it didn't run because "object already exists" but the object is not in the data base.

I created another job based on one of the tables with NO ties to anything and it still won't show up in after the replication.

If I go in and start job at step one for the job "functions" it will run no problem and the same for the job "Views", for some reason the job just won't start when timed.

I can give you more details when I get home, that's where I have Access from.

I log on as administrator to the terminal.

My statements start out with USE [Database Name] Go --- then the create view query or function Query

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-13 : 14:35:55

Are you sure you're in the right database?

What if you copy/paste the entire script into a query editor window and execute it?

The problem with GO seperators in T-SQL job steps is that a seperate statement can fail while the job step does not. For example:

USE db1;
GO

Create View aView
AS
SELECT	id, name, address
FROM	Customers; <-- fails (maybe I mistyped the table name)
GO

Create View anotherView
AS
SELECT	id, name, address
FROM	vendors; <-- succeeds
GO

Now the job step succeeds but my 1st view never got created!

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-13 : 15:23:46

When I copy/paste them in the query editor they run fine. No problems at all.

I'll double check and put up some of the log info when I get home.

If I rt.Click start job at step 1, it runs like a champ :)

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-13 : 17:03:41

I checked the Job Activity Monitor, every job "Succeeded". When I looked in the Database, there's NO views or Functions. So I copied pasted the code from the SQL Agent window to a new query and it ran successfully, and now shows the functions and views.

1st job: "There are two functions in this job...on is relevant to the views, the other is stand alone"
Schedule Type: Recurring - Enabled
Time: 06:45 am
Occurs: Daily
No End Date

2nd job: "There are 4 views in this job"
Schedule Type: Recurring - Enabled
Time: 06:55 am
Occurs: Daily
No End Date

In the connection properties, both are pointing to the correct database.

Any suggestions on what else to look at?

Larry

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-13 : 19:04:44

I'll reboot the server at this point, but sure we're missing some key pice of information.

Any chance the job hung and is still running? If so, right-click and stop it. Reboot at next maintenance window.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-06-13 : 19:26:31

Have you enabled logging within your Job steps? Also, are your SQL statements running in a TRY-CATCH block? There are some functions that illuminate the error that can only be invoked within a CATCH block. Lookup "ERROR_MESSAGE" and related topics in BOL for details.

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-13 : 19:48:57

Russell: I've contacted the administrator and asked him to change the creditals to his login to see if that helps. The roles I have are sysadmin...he has everything checked.

If you can tell what piece you think you need, I'll be happy to dig it up for you.

Bustaz: I haven't used the Try-Catch block, but I'll look it up. Thanks for the tip!!

I'll be in touch after I try some you suggestions!
Larry

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-13 : 23:04:47

sorry to ask the obvious but the obvious is sometimes the problem

1. what server context are you running this in?
2. what database context are you running this in?
3. When you do Create View aView, you do know it will fail if that view already exists, might you need to add drop view and then create?
4. What security are the jobs running under?
5. Why are you running this type of job to create views and functions every day sir? Sounds a bit off, you must have a special reason? Last time I worked at a place they did this was because they had the following tables that were create dynamically

payStubsMarch2008
payStubsApril2008
payStubsMay2008
payStubs5_2008

I suggested a calendar table or one payStubs table with month date year field. They sais what is that and why should we change it.
I said pay me now and let me get out here.
Do you have such a scenario? hence creation of views and functions on daily basis?

If you don't have the passion to help people, you have no passion

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-14 : 08:37:20

no need to be sorry! Ask away...I'm still learning every day about this stuff.

I think I've narrowed it down to the security. I ask the admin yesterday to run under his username.

I got up early this morning, change the time and waited. For whatever reason it worked today. So I'll be up early again tomorrow to make sure everything is properly running. I have logs enabled etc... so I can see where it fails.

I work for an Organ Procurement and transplant organization. They've purchased and online medical records website which gets replicated every morning at 5am. The replication doesn't append the database it completely wipes it out then restore the entire thing. So I set up the agent to run after the replication happens so the reports in SSRS will run properly. I didn't set up the database replication, just the jobs to run after replication.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-14 : 10:06:06

is this in production or in development? if in dev you can run at will

If you don't have the passion to help people, you have no passion

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-14 : 11:44:43

I think we've got it...the original schedule they told me about the replication was to end at 6:30 however as the database grew it's taking longer to run. The admin let me know today that it the replication isn't finished until 7am.

So I'll reschedule the jobs to start at 7:30 (just to be on the safe side)

I believe that will solve my headaches! :)

Thank you to everyone! I appreciate your input. I'm sure you'll see me on here again, hopefully for another issue.

Larry

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-06-14 : 14:42:39

quote:
Originally posted by LarryC74

I think we've got it...the original schedule they told me about the replication was to end at 6:30 however as the database grew it's taking longer to run. The admin let me know today that it the replication isn't finished until 7am.

So I'll reschedule the jobs to start at 7:30 (just to be on the safe side)

I believe that will solve my headaches! :)

Thank you to everyone! I appreciate your input. I'm sure you'll see me on here again, hopefully for another issue.

Larry

No offense but if the database keeps growing will you not have the same problem sometime in the future? What if the replication finishes at 7:35 two months from now?

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-14 : 15:20:11

None taken... :)

I will but they way I handled it at my hospital is I moved the times and haven't had any issues yet. That's not to say it won't happen.

I'll probably need to talk with the admin to have him move the replication to an earlier time. I'm not sure why they wait like they do. (I'm a contractor for them, writing SSRS reports)

Do you have any suggestions on how I should handle it if they decide not to move the time back?

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-06-15 : 10:07:08

I was hoping someone that had worked with replication would have posted some suggestions, sense I have no replication experience I don't know how the replication process is started.

That said, if the replication process is run from a SQL Server Agent job just put your job(s) as the next step(s) contingent on the replication steps completion.

If that is not the case (I'm sure there are better ways) but you could look for the last table(s) or index(s) that are replicated & if they are not there then generate an email to the on-call support person indicating such.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-15 : 10:53:05

Sqlraider, generally, you're right. What it sounds like Larry is doing is running the snapshot agent via a SQL Agent job every morning.

The reason you can't just make the create views/functions jobs the next step in the snapshot job, is you need to give the distribution agent time to apply the snapshot to the subscriber.

I suppose one could poll the distribution agent for the message "Delivered Snapshot..." then start the other steps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources