Here is a common scenario I bet you've had - if you are, or were a Database Administrator;
You receive an alert, indicating that a certain drive (either storage or local) is
running out of space.
And it's painted
RED of course, to make sure you're not smiling!
If your alerting system is configured to give you some 'grace' time (i.e. 1% free space left), you have just a little time to figure out which databases/tables are taking that space.
In this post, I am going to show some samples from our
Database File Explorer on how you can quickly troubleshoot such cases.
Big emphasis on the "some" - there is a full set of features not shown here.
So, let's begin on the main screen, showing me which drives are being used by my databases: (already letting you distinct between database space and other "stuff" on that hard drive)
Great! Now let's have a look on a graph showing me a pie slices for all databases.
Click on the Chart menu --
Choosing "Databases Size", and getting this:
OK. I can clearly see that there are two databases taking almost 100% of my disk space. Let's drill into one of them:
I've selected the 2nd largest database. If you look at the screenshot above you can see that it's using 2 drives (E:, F:)
Now note something interesting - When looking at the "Top 10 Tables by Size", I can quickly see that there is one table, taking 68% out of the entire database space.
I'm going to right-click the table and choose "Table Properties"
Getting a lot of useful information here, but something specific caught my eyes - note that this 85gb table is less than 20% data, and about 80% indexes!
So the next step would be drilling into the indexes in order to figure our which indexes are consuming so much storage space.
I'm going to click on the "Chart" button below:
And get this screen:
OK. See the last line on the graph legend? That's the "Data", and the rest is all index.
Going back to the previous window, I'm going to expend the "Indexes" node and get the list of all the indexes and their sizes/filegroups.
I've expended one of the indexes (taking almost 13gb) and saw it's built from multiple columns, where one (ApiKey) is indexed, while the rest are INCLUDED columns. This is a common problem, since included columns data is stored on the index leaf level. So while tempted to use for query performance reasons, including many fields - especially strings/blobs, will create
huge indexes!
To sum things up - I've used
Database File Explorer to quickly understand what's taking my disk space.
This is only a small part of what this app has to offer. (I will soon post a video showing a full demonstration of the product)
If you don't have it already, get it here:
http://doccolabs.com/products/dbfileexplorer
