One of the things that I’ve seen DBAs query for is free space in a database. In fact, this is one of the most common questions posted on SQLServerCentral by newbies that are trying to manage their instances and ensure they don’t use too much space or run out of space.
The most common way to do this is with sp_spaceused. This system stored procedure has been around for a long time and is commonly used. However, it’s cumbersome to me. I get two result sets, I get data formatted in both MB and KB, it’s hard to use for multiple databases, and incorporating it with some automated tracking gets annoying. Not hard, just annoying.
Enter dbatools. One of the cmdlets included is Get-DbaDatabaseFreespace and this does exactly what you’d expect. This takes the name of an instance as a parameter, and when you run it, you get a bunch of information about your databases and their space usage.
These are good numbers, but with PoSh, I can easily just get the data I want. For example, I can add a –Databases parameter and see specific databases.
I can also limit reformat the data in a table and limit what’s returned:
This flexibility to easily see data is one thing that I like about PosH. I could pipe this into a filter and look for those items that are of a certain size, or have a certain percentage value.
Putting this into a report, or even an alert that might get sent to DBAs is a great way to keep an eye on space and ensure that you don’t run out of space. Of course, tracking this over time is easier with a tool like SQL Monitor, but for quick checks, this dbatool is very handy.
If you haven’t played with these, I’d recommend you do this. This collection makes working with SQL Server and PoSh much easier.
Filed under: Blog Tagged: administation, dbatools, powershell, syndicated