The Defragmenting Code
The index-defragmenting code is composed of one script in two parts. The first part
scans the indexes to find tables that you need to defragment. The second part
, which contains the first part, generates all the statistics for the specified database's tables and indexes, and e-mails them to an administrator.
|Figure 1. The Initial Run: This first part of the script reports the tables that have fragmented indexes.|
You run the first part to locate the tables that require defragmenting. Choose a time when the system is mostly idle. To run the script in Management Studio (as shown in Figure 1), do the following:
- Choose Open from the File menu.
- Select File and locate the script.
- From the Database dropdown control, choose the database you want to check. Alternatively, you can add a USE mydatabase statement at the top of the script.
- Press F5 or click the Execute button.
Figure 2 shows the retrieved statistics from the resulting defrag report (against a local database). In addition, the script sends an e-mail containing the status and time of each occurrence to a specified recipient (see Figure 3). Be sure to update the script so that it sends the e-mail to an appropriate person within your organization.
When you're ready to defrag the indexes, you run the second part.
Figure 2. Defrag Statistics: The script generates a table named db_defrag for reporting the statistics on the specified database's tables and indexes.
Figure 3. E-mail to Admin: The script e-mails the specified administrator.