Have you ever been asked the question “quick, I need to know how much free space there is in the Nodrap database on server Cromulent”?
Ever wished that you could quickly grab that information?
You might well have a TSQL script that will grab that information for you. I’ve been working on pulling that information centrally, grabbing that data for multiple servers becomes a much simpler task when using PowerShell.
Why PowerShell and not TSQL?
For me the big bonus that PowerShell provides me for grabbing this data is that I can pull it quickly and easily, I can also simply run it for multiple machines and the same script works for versions SQL 2000 and up.
How big is my database?
Open up your favorite PowerShell ISE (you can even use the one that comes with Windows). If the aren’t already loaded you’ll need to add the SQL snapins
To get a list of databases, their sizes and the space available in each on the local SQL instance.
Very quick and easy. The information here is a little misleading though.
The Size is the size of the database and includes size of the transaction log(s). SpaceAvailable only represents the space available in the data files however, unless we’re looking at SQL 2000 in which case it’s the free space in the data and log files. To make things even more confusing Size is reported in MB and SpaceAvailable in KB.
To get more details we need to look at the transaction log information.
How big is my transaction log?
To grab this information we need to go deeper. In this case we will focus on the AdventureWorks database.
This information is all in KB which helps have it make a little more sense.
Doing the math
Now we have the size of the database, the size of the log and the free space in each it’s some quick math which gives us the sizing information.
- Size = Database Size in MB
- Size / 1024 (from log) = Log size in MB
- UsedSpace / 1024 = Log used in MB
- (Size – UsedSpace) / 1024 = Log free in MB
- Size – (Size / 1024) (from log) = Data files size in MB
- SpaceAvailable / 1024 = Space free in data files
- Size – (SpaceAvailable / 1024) – (Size / 1024) (from log) = Space used in data files
- Size – ((SpaceAvailable / 1024) – ((Size – UsedSpace) / 1024) = Space used in data files (SQL 2000)
Outputting nice results
Running all this and grabbing the data from different areas can lead to messy results. This is where the PowerShell DataTable come to the rescue.
A DataTable is a PowerShell object, much like a SQL table that can hold data for you.
First you create the object, define and add columns and then add rows before finally returning the data.
Here’s a quick example:
Putting everything together
Running the following script will pull the data and log information for all the databases on the SQL instance specified in the top parameter. It also handles named instances without modification, and is factored to return the correct information for SQL 2000 as well as higher versions
Note: Run DBCC UPDATEUSAGE for SQL 2000 instances to ensure that the data is accurate
Download the PS1 file and save it on your machine, from there you can call the script and just pass in the SQL instance to get the results eg:
In a follow up post I’ll show how we apply a minor tweak to the script and have the data loaded into a SQL database.