From time to time, history info on our scheduled jobs (SQL Server 7.0) is missing. When I try to view job history, I get a popup saying "There is no history information for this job". Sometimes the job does run, however, sometimes it doesn't... The problem seems to happen randomly on random scheduled jobs. Any tips on how to get back the consistency of info we used to enjoy in 6.5?
Also, is there a way to specify how many job runs the history should display. It only goes as far as 5 or so, I wonder if it can capture more "runs"?
Please email back the answer to lpinskaya@firstam.com - it would be GREATLY appreciated.
In SQL 6.5, when a scheduled job failed, you could see the error message in the history. In SQL 7.0, it simply tells you which was the last step to run. Is there a place which will report the actual error message generated by the task?
Is there a way to increase the number of records preserved in jobhistory? I have 82 jobs on my box, 30 of them run every 20 minutesfor 23 hours a day, every 30 days. Another 30 run once, every 30days. The system seems to have a limit of 50 history records for anyjob that hasn't run since yesterday and purges all history records ifthe job is more than a week old. I didn't know if there might be aconfiguration record buried in MSDB or somewhere else that would allowme to increase this size or perhaps a system SP that prunes jobhistory that I could modify.It isn't critical as the system is set to notify me when jobs fail,but still, I'd like to be able to look at a given day and verify thateverything went normally.
I'm thinking of using the SQL Agent Job Scheduler as part of a larger application and I'm wondering if anyone knows of a limit on how many schedules or jobs that can exist on a SQL Server at one time.
I am trying to figure out a way , looking at the tables in MSDB (SysJobHistory) that if a scheduled job is running and not completed yet , for how long it has been running. I have to look for all the jobs with run_status = 4 (in process). But what i figured out it no history is written till the job is finished or cancelled. Can anyone help me with this
I have a scheduled job that run one time a day at 10 pm. My problem is that if the job fail, i want that the job run 10 minutes after until it complete with success.
I am running a scheduled DTS which transfers all the rows in a production table to another server every 30 minutes,each time it truncates the table on the second server before transfer.It has been running fine for several days.Will there be any problem in this kind of backup strategy? Do I have to clear any history logs frequently?Or any other problem can happen? Can anyone suggest any precautions,as there will not be any down time allowed.Replication is also not acceptable by the client. Thanks.
I find some scheduled jobs are switching from an 'enabled' state to a 'disabled' state apparently for no good reason. The job itself still shows as being 'enabled', however the associated scheduled becomes 'disabled'. Does anyone why this would be? Is the problem associated with a paritcular service pack or anything??
I have a job that runs hourly every day from 7:00 to 19:00. I'd like to be able to detect the last run of the day. The problem is, I may change one (or both) or the scheduled run times, so I don't want to hard code 19:00 into my detection scheme.
I stumbled across the sysjobsschedules table in the msdb database, and I think the Next_Run_date and Next_Run_time fields will get me where I need to be.
I'm trying to build a second job that runs at 10 minutes after the hour, 24 hours a day that will somehow detect whether or not the primary job just finished it's last run of the day, and if so, insert some records into a table.
Here's what I have so far...
DECLARE @intFlg1 INTEGER SET @intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END FROM msdb.dbo.sysjobschedules WHERE (name = N'prodplan_importorders')) IF @intFlg1=0 INSERT INTO BLDOFF_INV_DAILY() SELECT GETDATE() AS Expr1, Product, Whse, Qty FROM BldOff_Inv_Hourly
The problem with this is that it will append records every hour after the last run until midnight. I only want it to append them once.
Its not possible to schedule jobs via Express is it? Like I have a process that connects to an Oracle system grabs the data... plays with it some and then inserts it into the MSSQL 2005 server... We need this process to run say every 2 hours... Express cant do that cant it?
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
I have this backup job created, this job is being called from VB once all the data is finished loading. You need to have SA permissions or be the database owner in order to run the jobs. How do I get around this, I don't want the user to be db owner or have sa privledges. Does anyone have any SP to get around this Thanks for any help
Hi all I'm stuck here, I hope someone else has come across a problem like this. About 12 scheduled jobs are running on SQL Server 7 (NT is the operating system) They are supposed to run under the security context of an account set up to run as a service for both SQL Server Agent and SQL scheduling Agent. This user account was given all the permissions that the person scheduling them has(that's me, also part of sysadmin role). Everything was running fine until one day the server crashed and after it came back up, nothing was working. The error I get is an odbc link error. I was even unable to open up the packages, so I had to recreate them from saved .dts files. I did come to find out that the NT domain admin password was changed as well, but why should that affect the user account under which these jobs run? Anyway, at the moment, nothing runs on the server and I can only manually execute them as packages from my local computer. Please help me to narrow down what could be the problem. The Agent service and sql server services are running so that's not the problem. thanks...
Does anyone has a script or knows how to find out whether the schedule job completed or not? If I schedule the job to backup all databases on server and e-mail me when job completes. But one time the job got stuck and somehow never completed. Is there a way I can schedule the job to see whether the other job completed or not?
Had to rename a SQL 2000 box and now the scheduled maint jobs that were created under the previous name of the box cannot be deleted via EM. Can anyone offer help?
We have DTS Packages that are scheduled to run nightly and show up in EM under JOBS under SQL Server Agent. A password got changed and some of the nightly jobs blew up.
Went into the Packages and entered the new password and successfully executed the packages BUT when I go back to jobs under the SQL Server Agent, the jobs STILL will not run - they fail with a login failure - as if i never fixed the DTS package. HELP
Can anybody help me in solving this issue. We are currently facing an issue in our scheduled jobs. We have a job scheduled in SQL Server 7.0, which executes SPs one by one. The job makes the database to single user mode and starts the clean up job (execute SP, which deletes old records from specified tables). Once this is done, the database is again converted to multi use.Our database size is 8 GB, there is a huge volume of data. our client started the job on one weekend and it continued till tuesday after noon, on wednessday morning, the job got failed.
Please help me in solving this issue. why the job taking so much time to run and how it can be monitored and tuned to work properly.
Hi Guys, I'm Hoping some one could help me out, I'm in need of creating a web interface to monitor scheduled SQL Jobs, both on sql 2000 & 2005. I'm new to asp, Can anyone point me in the right direction.Really appreciate it. Regards
i have a package scheduled in sql server..... if i change something in the package and save the change... will the scheduled job then reflect that change? or does the scheduled job have to be deleted and the package re-scheduled in order for the change to take affect? (for example....i have a scheduled package which pulls some data from an oracle server and populates a sql server tabel. well....the oracle server has changed. so if i open my package and change the oracle server connection and save it, will the scheduled job have this change?)
Getting this message when running a dtsrun step in a scheduled job;
DTSRun: Loading... DTSRun: Executing... Error: -2147220499 (800403ED); Provider Error: 0 (0) Error string: No Steps have been defined for the transformation Package. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700. Process Exit Code 1. The step failed.
This just started today.. These jobs have been running for months with no problems.
When I change the SA password on my server the current Scheduled SQL Jobs no longer work. If I run DTS packages by themselves and not from the Scheduled job window they work fine. I am using the sa as the Owner of the jobs and my SQL Agents services use a Domain Account. If I rebuild the jobs after the sa password is change they work fine. However, I don't want to rebuild 100 jobs every time I change my SA password.How can I fix or get around this problem without rebuilding my jobs?
We have scheduled jobs, which run every 2 minutes. If the job runs at 1:59am, the next run time is set to 2:01 am. If at 2:00am, the clock is rolled back to 1:00, my scheduled run time is still 2:01am. In this situalion, my 2 minute job will not run until 1 hour and 1 minute later, at 2:01am.
Any suggestions for reseting the next run times on the jobs to the next interval after the clock roles back to 1:00am.
Is it possible to add jobs into the SQL schedule by means of a SQL script. We have 1000 remote servers to update and don't really want to do it manually.
I have a job scheduled within Enterprise manager. How can I configure this job, within enterprise manager, to generate output files having the current system date and time as part of the output file names.
Is there a way to refresh AUTOMATICALLY each n seconds, the scheduled jobs instead of right clicking on the scheduled job and selecting 'refresh' ? An option in SQL server ? Or and never ending script that refreshes the scheduled jobs ?
In the notifications tab of the job I have e-mail operator selected. Click on the box with the elipses to enter the email addresses. It will let me enter 2 email addresses in the e-mail name field. It looks like the field has a limited length...is there a way to manually enter several addresses?
I have a sql server 2000 sp3a box with 4 named instances.
I have read all of the articles and I cannot get the 'send mail' stuff working.
I have a mailbox associated with the Domanin account that starts the sql server and sql server agent services. I installed Outlook and created a mail profile for this account. I bounced the SQL server agent on one of the Instances but when I go into the properties for the Agent, on the General Tab, the option to choose a mail profile is GREYED out. I tried 'xp_startmail' and I get this error: xp_startmail: failed with mail error 0x80040111
I have jobs scheduled on SQL Server 2005 and SQL Server 2008.
It's my understanding that if there are jobs scheduled to run between 2:00 - 3:00 AM this Sunday when Daylight Savings time moves the time from 2:00 AM to 3:00 AM, these jobs will run at 3:00 AM along with any scheduled jobs to run at 3:00 AM.
I am looking for a script to recreate scheduled jobs on new SQL Servers that I build. I am hoping to find one that will pull information from the system tables for the name of the server and the names of the databases so that it does not have to be extensively modified for each new server.
