One fine morning you go to your office as usual and in a happy mood, you start looking into your routine tasks. Till this time everything is fine and suddenly you come across an Job failure alert which says,
The first thing you look at is, the connectivity to the server (because the message says so) and you find the server is contactable and also all the databases are up and online.
So now the question is from where the error is popping?
Open the job and its step and see what it is doing and you find every thing is fine including the syntax.
But the section "Database" is blank which is supposed to have a value.
That triggers something in your mind and a possible cause for the job failure
What next?
The database section should have a value and this is the cause for the job failure.
This usually happens if a user database is used within this section and it has been renamed or deleted.
So, the best practice is to
| Date 4/14/2014 11:47:53 PM Log Job History (SimpleJob) Step ID 1 Server SANSLAB\SQL2008R2 Job Name SimpleJob Step Name SimpleStep Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Unable to connect to SQL Server 'SANSLAB\SQL2008R2'. The step failed. |
The first thing you look at is, the connectivity to the server (because the message says so) and you find the server is contactable and also all the databases are up and online.
So now the question is from where the error is popping?
Open the job and its step and see what it is doing and you find every thing is fine including the syntax.
But the section "Database" is blank which is supposed to have a value.
That triggers something in your mind and a possible cause for the job failure
What next?
The database section should have a value and this is the cause for the job failure.
This usually happens if a user database is used within this section and it has been renamed or deleted.
So, the best practice is to
- Choose a system database name for this section and use the name of user database in your syntax. It will help avoid the failures of this kind.
- Before renaming or deleting a database, make sure it is not referenced anywhere.
By doing so, even if the job fails it will give some meaningful error for us to troubleshoot further.
1 comment:
Some times this will happen due to database is in restoring state on which database your job running.
Post a Comment