Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community ForumsDatabasesMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old November 17th, 2005, 07:46 AM
chirimantecman chirimantecman is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2005
Posts: 2 chirimantecman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m 45 sec
Reputation Power: 0
Question Jobs, steps and schedules

Hi, I'm having the following problem:

I scheduled a couple of jobs to run every minute (independently) since about 3 weeks. They did so with no trouble until two days ago when at 19:53 they both ran for the last time. Here are the facts (for 1 of the jobs):

1 - job, step and schedule definition (logins and db names masked):

Use msdb

EXEC sp_add_job
@job_name = 'sp_revisar_com',
@enabled = 1,
@description = 'Controla ejecución de sp_revisar_com'
@owner_login_name = 'xxxxxx',
@notify_level_eventlog = 3

EXEC sp_add_jobstep
@job_name = 'sp_revisar_com',
@command = 'exec sp_revisar_com',
@database_name = 'xxxxxx',
@step_name = 'ejecutar'

EXEC sp_add_jobserver
@job_name = 'sp_revisar_com',
@server_name = 'server17'

EXEC sp_add_jobschedule
@enabled = 1,
@job_name = 'sp_revisar_com',
@name = 'sp_revisar_com_schedule',
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 0x4,
@freq_subday_interval = 2,
@active_start_time = '000000',
@active_end_time = '235959',
@active_start_date = '20051107'


2 - If I run sp_help_job on this job, I get:

Core Job Details:
=================
job_id B2654849-5700-466D-A0DE-0F35A54D75C5
originating_server server17
name sp_revisar_com
enabled 1
description Controla ejecución de sp_revisar_com
start_step_id 1
category [Uncategorized (Local)]
owner xxxxxx
notify_level_eventlog 3
notify_level_email 0
notify_level_netsend 0
notify_level_page 0
notify_email_operator (unknown)
notify_netsend_operator (unknown)
notify_page_operator (unknown)
delete_level 0
date_created 2005-11-07 15:13:11.190
date_modified 2005-11-16 08:57:18.010
version_number 17
last_run_date 20051114
last_run_time 195200
last_run_outcome 1
next_run_date 0
next_run_time 0
next_run_schedule_id 0
current_execution_status 4
current_execution_step 0
current_retry_attempt (unknown)
has_step 1
has_schedule 1
has_target 1
type 1

Job Steps:
==========
step_id 1
step_name ejecutar
subsystem TSQL
command exec sp_revisar_com
flags 0 (normal)
cmdexec_success_code 0
on_success_action 1 (quit with success)
on_success_step_id 0
on_fail_action 2 (quit with failure)
on_fail_step_id 0
server NULL
database_name xxxxxx
database_user_name NULL
retry_attempts 0
retry_interval 0
os_run_priority (normal)
output_file_name NULL
last_run_outcome 1
last_run_duration 1
last_run_retries 0
last_run_date 20051114
last_run_time 195200

Job Schedules:
==============
schedule_id 10
schedule_name sp_revisar_com_schedule
enabled 1
freq_type 4
freq_interval 1
freq_subday_type 4
freq_subday_interval 2
freq_relative_interval 0
freq_recurrence_factor 0
active_start_date 20051107
active_end_date 99991231
active_start_time 0
active_end_time 235959
date_created 2005-11-07 15:17:11.160
schedule_description (Description not requested.)
next_run_date 20051114
next_run_time 194400


3 - If I run the stroed procedures that the steps execute, they run normally.

4 - If I run the jobs by hand, they return a value of 0 (job started succesfully) but the underlying stored procedure does not execute (I can check this).

5 - SQL Server Agent is running.

6 - Our DB service is external, the server isn't ours. They tell me they haven't made any changes and that the server hasn't stopped running.

Well thanx for any help in advance!

Reply With Quote
  #2  
Old November 17th, 2005, 11:25 AM
chirimantecman chirimantecman is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2005
Posts: 2 chirimantecman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m 45 sec
Reputation Power: 0
For those about to read...

Well, answering myself, for anyone who might be going through the same trouble:

restarting MSSQL Server and the Agent fixed it up.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft SQL Server > Jobs, steps and schedules


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway
Stay green...Green IT