SQL Server Agent Interview Questions

By:   |   Updated: 2012-01-27   |   Comments (3)   |   Related: More > Professional Development Interview Questions DBA


Problem

In the latest installment of the SQL Server interview questions, we will outline questions suitable for a SQL Server DBA interview to assess the candidates skills related to SQL Server Agent. Good luck!

Solution

Question Difficulty = Easy

  • Question 1: What sorts of functionality does SQL Server Agent provide?
    • SQL Server Agent is a Windows service that accompanies each instance of SQL Server on a machine for most editions of SQL Server.
    • SQL Server Agent is primarily a job scheduler for executing T-SQL, SSIS, DOS, etc. scripts.
    • SQL Server Agent is also responsible for defining Operators and Alerts.
      • Operators can be associated with Jobs or Alerts, so that particular people (email addresses, pagers, NET SEND) are notified or distribution lists are notified if an issue occurs.
      • Alerts can be setup for custom conditions or errors of a particular severity level.

 

  • Question 2: Do all of the SQL Server 2005, 2008, 2008 R2 editions install the SQL Server Agent service by default?
    • No - The SQL Server Express Edition does not have a SQL Server Agent Service.

 

 

  • Question 4: True or False - Can a single Job have multiple Job Schedules?
    • True.

 

 Question Difficulty = Moderate

  • Question 1: How many options are available to identify failed jobs?
    • Manually review the failed Jobs in Management Studio.
    • Setup an automated process to query the msdb.dbo.sysjobhistory system table to find the failures.

 

  • Question 2: How many of the SQL Server Agent system tables can you name with their associated purpose?
    • sysjobactivity stores data about job activity
    • sysjobhistory stores data for all historical executions of all jobs
    • sysjobs stores data about each job such as the name
    • sysjobschedules stores job schedule information
    • sysjobservers stores server information related to a job
    • sysjobsteps stores specific job step information such as the type of code being issued, the actual code, etc.
    • sysjobstepslogs stores specific job step log information for each run if this is enabled.

 

  • Question 3: How many of the SQL Server Agent system stored procedures can you name with their associated purpose?
    • sp_help_job
      • This stored procedure returns information about the job. 
      • If no parameters are used information is returned for all jobs. 
      • If a specific job_id is passed it gives you job information, job step information, schedule information and last run information.
    • sp_help_jobactivity
      • This stored procedure returns information about the status of the job run. 
      • If no parameters are used information is returned for all jobs.
    • sp_help_jobcount
      • This stored procedure gives you a count of how many jobs a schedule is tied to. 
      • This stored procedure requires either @schedule_id or @schedule_name to be passed as a parameter.
    • sp_help_jobhistory 
      • This stored procedure returns all history information for all of the job runs. 
      • If no parameters are used information is returned for all jobs.
      • If you also use parameter @mode = N'FULL' this provides additional information about each job step.
    • sp_help_jobs_in_schedule
      • This stored procedure gives you a list of the jobs that are tied to a schedule. 
      • This requires either @schedule_id or @schedule_name to be passed as a parameter.
    • sp_help_jobschedule
      • This stored procedure provides jobs schedule information for a particular job.
      • This stored procedure requires either @job_id or @job_name to be passed.
    • sp_help_jobserver
      • This stored procedure provides information about a specific server tied to a job. 
      • This stored procedure requires either @job_id or @job_name to be passed.
    • sp_help_jobstep
      • This stored procedure provides information about the job steps for a specific job. 
      • This stored procedure requires either @job_id or @job_name to be passed.
    • sp_help_jobsteplog
      • This stored procedure returns information about a specific job step log. 
      • This stored procedure requires either @job_id or @job_name to be passed.
    • sp_get_composite_job_info
      • This stored procedure returns combined data for all jobs in the system.
      • If no parameters are used info is returned for all jobs.
    • Additional information - Accessing SQL Server Agent Data

 

 

  • Question 5:  True or False.  Besides the MSDB database, SQL Server Agent also has configuration parameter related data stored in the registry.

 Question Difficulty = Advanced

  • Question 1: What is multi-server administration and when would you use it?
    • Job management paradigm with a master server and one or more target servers.  The master server sends and receives jobs from the target servers with all of the job and job step related information stored on the master server.  When the jobs complete on the target servers notification is sent to the master server so this server has the updated information.  Multi-server administration is used in a enterprise environment where a consistent set of jobs need to run on numerous SQL Servers, this technology helps to consolidate the creation, execution and management of those jobs.

 

  • Question 2: What is a SQL Server Agent Proxy?  Can you name some of the sub-systems proxies?  Why are the proxies of any significance?
    • A SQL Server Agent Proxy is an account that is setup to help secure a particular sub-system, so that if an login\user is trying to access the particular sub-system and does not have rights, the proxy will grant rights.
    • The SQL Server Agent Proxies include:
      • ActiveX Script
      • Operating System (CmdExec)
      • Replication Distributor
      • Replication Merge
      • Replication Queue Reader
      • Replication Snapshot
      • Replication Transaction-Log Reader
      • Analysis Services Command
      • Analysis Services Query
      • SSIS Package Execution
      • Unassigned Proxies
    • The SQL Server Agent Proxies offer a new level of granularity for SQL Server Agent that was not previously available.
    • Additional information - SQL Server Agent Proxies

 

  • Question 3: What are the new SQL Server Agent Fixed Database Roles and what is the significance of each role?
    • SQLAgentUserRole - Ability to manage Jobs that they own
    • SQLAgentReaderRole - All of the SQLAgentUserRole rights and the ability to review multi-server jobs, their configurations and history
    • SQLAgentOperatorRole - All of the SQLAgentReaderRole rights and the ability to review operators, proxies and alerts, execute, stop or start all local jobs, delete the job history for any local job as well as enable or disable all local jobs and schedules
    • Additional information - <-- SQL Server Security Note --> SQL Server Agent Fixed Database Roles

 

Next Steps
  • SQL Server Agent is a core business component for many applications and core business processes.  Knowing the basics about this feature of SQL Server will reap numerous benefits as an option to address a business need.
  • If you are searching for a SQL Server DBA or Developer opportunity, be sure to brush up on SQL Server Agent as well as many of the other core application components.
  • Check out these related tips:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-01-27

Comments For This Article




Tuesday, May 16, 2017 - 3:06:30 PM - [email protected] Back To Top (55818)

 

 This was so useful, great job

 

* NOTE ** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.

Friday, January 17, 2014 - 7:40:18 PM - Jeff Moden Back To Top (28130)

Oddly enough, I've only had to ask 2 DBA candidates anything about SQL Server Agent because the other 30 or so never made it that far. ;-)  And, no... I don't ask a bunch of nasty, esoteric, ace-breaking, oolie, "bet I'm smarter than you" questions.  I'm totally amazed at how ignorant of the job that so many people listing "SQL Server DBA" on their resumes actually are.

Thursday, October 31, 2013 - 12:32:08 AM - P.phani.chandrika Back To Top (27345)

hi,

 

these interview questions are very useful to me, can u provide realtime problem questions also.

thank u

 

 














get free sql tips
agree to terms