You are a database administrator for your company. You are managing the production database Prod1 thatresides on the SQL Server 2008 instance named Sql1.
You perform the following tasks on Prod1 as a part of your daily activities:
? Perform a differential database backup at 8:00 P.M.
? Rebuild indexes on database tables at 9:00 A.M.
? Perform a backup of the transaction log in the database at 12:00 P.M.
? Update the database statistics at 8:00 A.M.
You want to automate these tasks to occur at the scheduled time on all weekdays. What should you do? (Choose two. Each correct answer represents part of the solution.)
A.
Ensure that the SQL Server Agent is running.
B.
Ensure that SQL Server Reporting Services is running.
C.
Create one job for all the tasks, and schedule each task to run at the desired time.
D.
Create four different jobs for the tasks, and schedule each job to run at the desired time.
Explanation:
You should ensure that the SQL Server Agent is running, create four different jobs for the tasks, and scheduleeach job to run at the desired time. When you create a job in SQL Server, you can run the job at a particular time.You cannot schedule the different tasks within a job to be performed at different times. If you want each task to beperformed at a different time, you should create one job for each task. The SQL server will then perform thesetasks at the scheduled time. Additionally, to enable the SQL server to perform the tasks successfully, the SQLServer Agent should be running. If the SQL Server Agent is not running, the scheduled jobs will not be run. TheSQL Server Agent is the service responsible for executing scheduled SQL administrative tasks. After each of the jobs is created, you can change the properties of the job by viewing the
Job ScheduleProperties dialog box. To disable a job, you can clear the Enabled check box in the Job Schedule Properties dialog box. Disabling a job is a better choice than deleting the job if you think you may need to re-enable the job inthe future. The Enabled check box is shown in the following image:You can also change the schedule type and schedule frequency from this dialog box and configure the durationfor the job. The Schedule type option can be configured with one of the following settings:
? Start automatically when SQL Server Agent starts
? Start whenever the CPUs become idle
? Recurring
? One time
The Frequency section only becomes available if the Recurring schedule type is selected. You should not ensure that SQL Server Reporting Services is running. SQL Server Reporting Services is notrequired to perform scheduled tasks on SQL Server. SQL Server Reporting Services is a server-based tool thatcan be used to create reports that contain data from different sources, such as SQL Server, Analysis Services, orMicrosoft .NET data providers. You can create different types of reports using SQL Server Reporting Services,such as tabular, matrix and free-form reports. You should not create one job for all the tasks and schedule each task to run at the desired time. You cannotschedule different tasks within a job to run at different times. All the tasks in a job must be scheduled to run at thesame time. If you want to schedule different tasks to be performed at different times, you should create a separatejob for each task.Objective:
Maintaining SQL Server InstancesSub-Objective:
Manage SQL Server Agent jobs.References:
MSDN > MSDN Library >Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > AutomatingAdministrative Tasks (SQL Server Agent) > About SQL Server Agent TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > AutomatingAdministrative Tasks (SQL Server Agent) > Implementing Jobs > Creating Jobs