Knowledge Base Article

SQL Database Fragmentation Level - Email Reporting

Hello All

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.

https://www-secure.symantec.com/connect/ideas/programmatically-check-enterprise-vault-sql-database-fragmentation-and-report-it-vac-within-sy

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:

  1. The script takes the input from file EVSQL.txt. This file can contain single server or multiple server names as required.
  2. 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.
  3. 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.
  4. 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.
  5. 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:

  1. 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")
  2. 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
  3. 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. :-)

Published 13 years ago
Version 1.0
Basics
Enterprise Vault
Good Script
Information Governance
Monitoring
Reporting
  • AndrewB's avatar
    AndrewB
    Moderator
    13 years ago

    Thanks for posting this. It's a really nice concept. Here's my (unsolicited) feedback :-)

    For me, the included sample report didnt work because it's missing some sheets?

    For the PS1 I changed the 2 file path definitions to ./ instead of C:\ so they get created in the same dir as the script and not at the root of C.

    I ran this in my lab where my SQL server does a bunch of stuff, not just EV. I wonder if you can enumerate EnterpriseVaultDirectory for all the related EV databases instead of running the script against anything that's on the specified SQL server.

    Thanks again.

  • AKL's avatar
    AKL
    Level 6
    13 years ago

    Thanks for feedback Andrew, I modified the HTM report from my production using excel so it messed up somewhere I guess. Sorry about that.

    You can modify below line to focus the report only on EV databases:

    if ($db.IsSystemObject -ne $True -and $db.IsAccessible -eq $True -and $db.Name -like *EnterpriseVault*)

    Deleending on the names for EV databases, you can modify the $db.Name filter - And it should only check & report for EV databases.

  • AndrewB's avatar
    AndrewB
    Moderator
    13 years ago

    This did the trick:

    if ($db.IsSystemObject -ne $True -and $db.IsAccessible -eq $True -and $db.Name -like '*EV*' -or $db.Name -like 'EnterpriseVault*')

    thanks for the tip.

  • ChrisLangevin's avatar
    ChrisLangevin
    Level 6
    8 years ago

    This script got some attention in another thread and I recommended a couple modifications. I thought I would post the link here for anyone following the original.