Database Reference
In-Depth Information
Figure 13-25.
Entering a job schedule
Schedule this stored procedure to execute during nonpeak hours. To be certain about the usage pattern
of your database, log the
SQLServer:SOL Statistics\Batch Requests/ sec
performance counter for
a complete day. It will show you the fluctuation in load on the database. (I explain this performance
counter in detail in Chapter 2.)
7.
Return to the New Job dialog box by clicking the OK button.
8.
Once you've entered all the information, click OK in the New Job dialog box to create
the SQL Server job. A SQL Server job is created that schedules the
spIndexDefrag
stored
procedure to run at a regular (weekly) time interval.
9.
Ensure that SQL Server Agent is running so that the SQL Server job will run automatically
according to the set schedule.
The SQL job will automatically analyze and defragment the fragmentation of each database every Sunday at 1 a.m.
Figure
13-26
shows the corresponding output of the
FragmentationOutput.txt
file.