I had to prepare the list of tables having more than 60% fragmentation. I wrote this query to get those information. The below query will give the list of all the fragmented tables in a particular database having more than 60% fragmentation. This will work for SQL 2005 and beyond.
I decided to automate this and put it as a job across all the servers. The following needs to be done to schedule it as a job:
- Right click on the db you wish to know the fragmentation status – Task – Export data
- Choose the source server and dbname and click next
- Choose the destination as Excel and specify the path where you want the o/p excel file to be placed
- Choose the option ‘Write a query to specify the data to transfer’
- Click Next, Next and finally in the Save and execute package page please choose the option ‘Save SSIS package’ instead of execute immediately
- Choose file system and mention the path where you want the dtsx file to be saved.
- Click finish to save the SSIS package
Now follow the below steps to configure the package as a job:
In the 1st step of the job select the type as Operating system (cmdexec) and copy paste the below query: Please change the path of the dtsx file to the path you have saved. In my case i have saved it to C:temp location.
C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /f “C:TempIndex_Fragmentation Report.dtsx” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E
Step2: Use the following command to send an email of the excel sheet containing the fragmentation details of tables
Step3: Use the below command as step 3 in the job to delete the excel file once its mailed to the intended recipients.