This is a bit of a departure from "SQL Server"-oriented functionality that I have been describing in recent posts. In some shops, free disk space or, more likely, the lack of it is a daily battle. Every morning, I run a script that checks for many different kinds of "negative events" that might have happened over night. A "negative event" might be a failed job, database growth or a server running low on disk space. The latter check calls Get-SQLServerList and Get-LogicalDisk to do all of the heavy work.
The script is straightforward, so I'll just continue with my tradition of a brief overview with some examples. (This is a blog, after all, and not really suited to voluminous detail. FWIW, it is my opinion that detail on what a confusing part of a script does should be in the comments in the script, not in a blog posting. If you find a part of any script confusing, let me know and I'll update the comments in the script and possibly do a blog posting to explain how it works.)
The function takes a list of computers as pipeline input, so feeding a list of SQL Servers using Get-SQLServerList (which I normally alias as "ssl") or any other method is easy. Get-SQLServerList uses WMI to retrieve the following information from each "fixed disk" on the remote system:
- Computer
- Letter
- Label
- Space, which gives the total size of the disk
- FreeSpace, which gives the size of the free space on the drive
- UsedSpace, which gives the size of the space which is being used on the drive
- Free-%
- Used-%
- Unit
I would like to point out that one could calculate UsedSpace by subtracting FreeSpace from Space and that one could calculate Free-% given Used-%, yet they are returned by Get-SQLServerList. These calculations are done by Get-SQLServerList so a calling program (or something that you've hacked together on a command line) do not have to. This makes for a more flexible function and for less typing.
Here is a simple example, including some output, which finds information on the local computer:
PS> get-logicaldisk . | ft -a
Computer Letter Label Space FreeSpace UsedSpace Free-% Used-% Unit
-------- ------ ----- ----- --------- --------- ------ ------ ----
MYLAPTOP C: local.boot 33.17 7.94 25.23 24 76 GB
If you are using a naming scheme (or some other method) to discriminate between development, quality assurance and production servers, the following query might provide a list of disks where you might be able to store that 'temporary' backup:
PS> ssl de | Get-LogicalDisk | where {$_.Letter -match "c"} | sort FreeSpace | ft -a
Here is a command that looks for disks on production servers with less than 5% free space:
PS> ssl pr | Get-LogicalDisk | where {$_."Free-%" -lt 5} | ft -a
I hope that you can see the power of the Get-LogicalDisk function, which can easily be leveraged by additional simple Powershell syntax to provide complicated, real-time reports on disk space issues.
No comments:
Post a Comment