May 9, 2007 at 7:52 am
In a busy production environment, i set up few jobs which run during night time. Some time these jobs run more than expected time resulting failure of other jobs which run after. Is there a way i can setup up a time limit or expiry time which will stop the job after that specified it if exceed? For eg. x job runs at 10.00 pm and y job runs at 12.00 am. The expected time of x job run is around 30 mins but some time it runs for more than 2 hours resulting failure of y job. Can i set a stop time for x job of 60 mins. So that the system will stop x job if it runs for 60 mins?
Thanks,
May 9, 2007 at 8:59 am
What does job x do? Bear in mind that if you want it to stop, you will probably want it to roll back as well... which could easily take longer than it would actually take to finish running the job. Therefore you will need to build this logic into the job itself. For example, if your job is to reindex your tables one at a time, you could put some code in that will stop the job if the elapsed time is more than 60 minutes. But if you are importing data from another database in batches of 50000 rows, you will need to remove those rows that have already been imported if you stop the job half way through.
John
May 9, 2007 at 9:34 am
Thanks. The job x is actually a backup job which need to restrict to 60 mins.
May 9, 2007 at 9:46 am
Job y must be pretty important if it's worth trashing a backup for! Have you done any investigation into why your backup sometimes takes 30 minutes and sometimes 2 hours? Maybe it's the transaction log: if it's really big just before you do the backup, the backup will take longer. You could try truncating it before the backup. But bear in mind that you won't be able to recover to a point in time between the truncation and the end of the full backup. Another thing you can try is a full backup once a week and incremental backups nightly, or just backup one filegroup every night if your database has its data suitably distributed. Or how about combining the two jobs so that job y becomes step 2 in job x? Then they will run one after the other.
John
May 9, 2007 at 11:57 am
My backup schedule is full-diff-tlog bkp. Sometimes my Diff backup takes long time and resulting error: error in reading bkp device. I don't understand why this happens.
May 10, 2007 at 2:23 am
So I think the most important thing is to find out why you are getting the error. Are you backing up to tape or to disk? Is it to a local device or a network device? Please will you post the BACKUP DATABASE statement from your job.
John
May 10, 2007 at 6:43 am
Full Backup:
BACKUP DATABASE [ABC] TO [ABC_BackUp] WITH INIT, NAME = 'Full Bkp'
Verify Backup:
RESTORE VERIFYONLY
FROM DISK = 'F:\Database Backup\User Databases\ABC\ABC.BAK'
Above is the backup script, sometimes the full backup takes very long time of approx 9.5 hours and even verify bkp is also taking much time then expected. My db file size is 113715 MB.
Once backup is completed when i try to read bkp device the system gives me following error,
Cannot open backup device 'TelehopBilling_BackUp'. Device error or device off-line. See the SQL Server error log for more details.
May 10, 2007 at 6:51 am
Hi,
if you do
BACKUP DATABASE [ABC] TO DISK = 'F:\Database Backup\User Databases\ABC\ABC.BAK' WITH INIT, NAME = 'Full Bkp'
does that work?
regards karl
Best regards
karl
May 10, 2007 at 7:02 am
Yes, or replace your verify statement with this:
RESTORE VERIFYONLY FROM 'ABC_BackUp'
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply