SQL Database Fragmentation Level - Email Reporting
Few weeks ago, I was reviewing an enhancement idea posted on below forum, which stated to have feature integrated with Enterprise vault system daily check to also cover the EV SQL database fragmentation such that any administator can see that within VAC and action appropiately.
I agree that it would be a great feature to be added moving forward, but meanwhile, we can use Powershell & SQL reporting to generate reports which covers details of fragmentation at table & index level within each Enterprise vault database.
Attached with this article, you'll find one such script, which takes SQL server name as input using EVSQL.txt file and generates HTML email report that covers fragmentation & page count level for each table & index within each database on that SQL server or instance.
The script will mark any fragmentation level higher than 30% & Page count higher than 1000 as Red, so that DBA or EV administrator can identify that table easily & act on that accordingly.
How it works:
- The script takes the input from file EVSQL.txt. This file can contain single server or multiple server names as required.
- The script than loads SQL 2008 DLLs for running its queries and iterates through all database & all tables within for all servers mentioned in the txt file.
- The script queries for fragmentation level & page count for each Table & index value within particular database accordingly and return any error within the powershell window.
- The script is designed keeping in mind that not all people have SQL powershell installed. Hence, you can run this script from any remote computer which has Powershell installed on it as long as it is able to Connect to each SQL instance mentioned in the txt file.
- Once the report complete, it uses an exchange server to send out report over email using anonymous authentication. Make sure the exchange server you use here has receive connector that supports anonymous authentication, else you'll have to rely on manual HTML file copy that is generated.
Things you add:
- You can change the threshold value for items marked as red. I used fragmentation level as 30 and Page count as 1000. You can modify it in below line: If ($frg -gt "30") and If ($dbpc -gt "1000")
- You can also integrate auto fragmentation of indexes within same script. It didn't suit my environment so I didn't do so, but if you wish you can. A script that defines function for auto fragmentation after certain level is available at http://sqlblog.com/blogs/allen_white/archive/2010/11/13/scripting-index-maintenance-with-powershell.aspx
- You can create a bat file which calls Powershell.exe and the PS1 script and run it on a scheduled basis as required.
Please find script, a sample txt file and a sample output attached with this article. Hopefully you will find it helpful and I am here for any questions. :-)