• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MS SQL Server/SQL Server JOBS
Popular Search:Oracle, SQL Server, MongoDB

SQL Server JOBS

79 views 0 September 7, 2019 April 2, 2021 admin

Jobs in SQL Server

SQL Server JOBS

JOBS: JOBS ARE SERVER LEVEL OBJECTS USED TO PERFORM AUTO EXECUTION OF GIVEN STATEMENTS / TASKS
ON A SCHEDULED BASIS. FOR EXAMPLE: PERFORM A FULL BACKUP EVERY DAY AT 12 AM.

PURPOSE OF JOBS:
TO AUTOMATE ANY SQL SERVER OPERATION
EX: ROUTINE FULL BACKUPS

ENTITES IN A JOB:
1. JOB STEP – TO SPECIFY THE ACTIVITY / STATEMENT TO BE EXECUTED IN A JOB WHAT?
EX: TO PERFORM A FULL BACKUP

2. JOB SCHEDULE – TO SPECIFY THE FREQUENCY OF JOB EXECUTION FREQUENCY?
EX: TO EXECUTE / RUN THE JOB EVERY 24 HOURS

3. JOB NOTIFICATION – TO NOTIFY / SEND EMAIL ABOUT JOB STATUS (SUCCESS or FAILURE) REPORT?
EX: TO SEND EMAIL IF JOB FAILS

————————————- PRACTICE ITEMS —————————————-

STEP 1: HOW TO CREATE JOBS ?
LAUNCH SSMS > CONNECT TO YOUR SERVER.
ENSURE THAT YOUR SQL SERVER AGENT IS STARTED.
IF NOT, LAUNCH “SQL SERVER CONFIGURATION MANAGER” TOOL > START THE SQL SERVER AGENT SERVICE

Now, RIGHT CLICK SQL SERVER AGENT > NEW JOB > GIVE A NAME,.
GO TO STEPS >> NEW STEP >> NAME. DEFINE THE ACTUAL OPERATION (EXAMPLE : BACKUP SCRIPT)
GO TO SCHEDULES >> NEW SCHEDULE / PICK EXISTING SCHEDULE > OK
GO TO NOTIFICATIONS >> WRITE TO WINDOWS APPLICATION LOG > OK

STEP 2: HOW TO EXECUTE THE JOBS ?
EXPAND SQL SERVER AGENT > JOSB > RIGHT CLICK JOB >> START JOB AT STEP

STEP 3: HOW TO CHECK JOB HISTORY ?
RIGHT CLICK JOB >> VIEW HISTORY

STEP 4: HOW TO CHECK FOR JOB ERRORS ?
RIGHT CLICK DATABASE >> TASKS >> TAKE OFFLINE
THEN EXECUTE THE JOB. WE SEE THE JOB FAILURE.

RIGHT CLICK JOB >> VIEW HISTORY >> EXPAND JOB STEP >>
SCROLL THE BOTTOM PANE TO READ COMPLETE ERROR DETAILS.

RIGHT CLICK DATABASE >> TASKS >> BRING ONLINE
THEN EXECUTE THE JOB. WE SEE THE JOB SUCCESS.

STEP 5: HOW TO CONFIGURE DATABASE MAIL ?
SSMS > CONECT TO SERVER > MANAGEMENT > RIGHT CLICK DB MAIL > CREATE NEW PROFILE.
SET A PROFILE NAME.
ADD N EMAIL ACCOUNT > SPECIFY SMTP [SIMPLE MAIL TRANSFER PROTOCOL] EMAIL SERVER DETAILS
FINISH.

RIGHT CLICK AGENT > PROPERTIES > ALERT SYSTEM > ENABLE MAIL PROFILE.
SELECT THE EMAIL PROFILE CREATED ABOVE.

STEP 6: HOW TO DEFINE “EMAIL OPERATORS” AND LINK THEN TO JOBS ?
FROM AGENT > OPERATORS > NEW OPERATOR > SPECIFY NAME, EMAIL RECEIVER ADDRESSES. OK.

RIGHT CLICK THE JOB CREATED ABOVE > PROPERTIES > NOTIFICATIONS >
SELECT ABOVE DEFINED OPERATOR, SELECT SUCCESS/FAILURE

STEP 7: HOW TO CREATE ALERTS ?
EXPAND SQL SERVER AGENT > ALERTS > NEW ALERT > SPECIFY THE REQUIRED CONDITIONS.

STEP 8: HOW TO USE OPERATORS FOR ALERTS ?
IN THE NEW ALERT CREATION PAGE > “RESPONSE” PAGE > SELECT THE REQUIRED OPERATOR.

STEP 9: HOW TO DEFINE MAINTENANCE PLAN JOBS?

LAUNCH SSMS > CONENCT TO SQL SERVER > START SQL SERVER AGENT.

FROM SERVER >> MANAGEMENT >> MAINTENANCE PLAN >> NEW MAINTENANCE PLAN WIZARD > SELECT BELOT TASK ITEMS

HEALTH CHECK TASK
FULL BACKUP
DIFFERENTIAL BACKUP
LOG BACKUP
MAINTENANCE CLEANUP
HISTORY CLEANUP

FOR EACH TASK ABOVE >> SELECT ALL DATABASES, IGNORE DATABASES WHICH ARE NOT ONLINE
SCHEDULE THE ABOVE JOB BY CLICKING @ CALENDAR OPTION
FINISH.

FROM ABOVE STEPS, A SET OF MAINTENANCE PLAN JOBS ARE AUTO CREATED.

STEP 10: HOW TO EXECUTE & TEST MAINTENANCE PLAN JOBS?
JOBS ARE AUTO EXECUTED AS PER THE SCHEDULED TIME.
OR
RIGHT CLICK JOB > START JOB AT STEP. THIS EXECUTES THE MAINTENANCE PLAN JOB.

Was this helpful?

Yes  No
Related Articles
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • Backup using T-SQL
  • Steps to Take Backup from GUI in SQL Server
Leave A Comment Cancel reply

MS SQL Server
  • SQL Server JOBS
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • Backup using T-SQL
View All 20  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org