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.