 | Note: Ensure that you are logged in using the AgfaService account; you cannot log into SQL Server Management Studio using the Administrator account. |
To keep the databases running efficiently and improve performance, a scheduled job is created, which will check the index defragmentation process and rebuild them if needed. By default, the process is scheduled to run every Saturday at 2:00.
| Note: If you change the default time for the defragmentation job, ensure that the new time is set after the scheduled database backup job and before the scheduled statistics collection job (schedule_stats). Because the process may require significant resources, schedule the defragmentation process during non-peak hours. |
The defragmentation job rebuilds indexes on user databases but does not affect system databases, such as the master or msdb database.
To rebuild the indexes, the process defragments the leaf level of each index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes and compacts the pages of the index. Any empty pages created as a result of this compaction are removed.
To schedule the defragmentation job to rebuild SQL 2005 or SQL 2008 database indexes
Open the SQL Server Management Studio.
In the Object Explorer window of the Management Studio, expand server > SQL Server Agent > Jobs
where server is the name of the SQL Server that the program is running under.
Right-click defrag_job and select Properties.
In the Properties dialog, switch to the Schedules tab.
Select the job and click Edit.
In the Job Schedule properties dialog, change the scheduled time.
To save the change and close the dialog, click OK.
To close the Properties dialog, click OK.