SQL database backup success but - causing connection errors for scheduled tasks

Hi there,

I have an sql server which runs its database backups at 22:00.

At this time we have a number of scheduled tasks which connect to the database and extract some information.

The backups run perfectly but the scheduled tasks intermittently fail with sql connection errors.

If we change the scheduled tasks to run an hour before they work perfectly so its defintly the backups causing issues.

What can i check to resolve?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Do these scheduled tasks run via the OS Task Scheduler? (From your statement that they "connect to the database", it sounds like they do.)

If so, you can actually trigger them off the completion of a SQL Maintenance Plan (if that's how you run your backups).

For instance, when a Maintenance Plan completes, it will log an event to the application event . If you look for source SQLISPackage100, Event ID 12289 (could differ a little bit, you'll have to play with this), your OS Task could fire from this trigger.

If these are all run within SQL Agent (as jobs), you may have to consider adding a sp_start_job 'Some Next Job' in your last step of the backup.

This is one of a number of articles on sp_start_job. I also wrote a blog sometime back on Scheduling OS Tasks to run after a maintenance plan finishes.
Spikeuk30Author Commented:

Yes OS Task Scheduler.

This runs an exe which is configured to connect to the db pull some information and email it.

Probably a silly question   -  Why will it not work during the maintenance plan?  

Is the db avalible to website users while the paln is running?
dsackerContract ERP Admin/ConsultantCommented:
Depend on where the backup is at. Sounds like you possibly hit a deadlock.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Networking

From novice to tech pro — start learning today.