0If you are lazy and not intended to do repetitive manual work at a routine time, then SQL Server Agent job help you achieve this. SQL Server agent jobs help to automate the execution of the recurring task in the SQL Server database at a scheduled time.
It is important that DBA should have a clear idea about how to troubleshoot when the job gets failed.
SQL Agent can automate the execution of multiple tasks in a single or by creating multiple jobs. Below are few things which SQL Agent job can handle or categorise.
1. Execute TSQL Query
2. Execute Maintenace Plan (SSIS)
3. SSIS Package
4. Execute Powershell script.
5. Database Integrity check
6. Replication agent
7. Log-shipping
How DBA can help resolve or troubleshoot the failure of SQL Jobs?
- DBA should use Job activity
- Check for last run status
- Find out the error resulting in failure.
Some common reason for job failure
1. Insufficient Permission provided to the job owner.
2. Script error / Incorrect syntax /Object missing/ Source file missing
3. Primary foreign key relation error
4. Deadlock/Blocking
5. Insufficient space on the drive
6. Restricted growth of database (data, Log file)
7. Linked server permission issue
8. LSN mismatch in case of log shipping
If you are not able to find the error, you should add logging to the job which can be done in advance setting on job steps. Apart from the listed error, DBA should check the SQL Server Error log to find the RCA.
