on 08-22-2012 03:33 PM
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.
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:
Things you add:
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. :)
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.
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.
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.
Thank you for sharing the Powershell script here AKL, I'll give it a try now.
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.