SQL Server Instance – database files location, disk, mount point, size, free space, xp_fixeddrives looklike

Goal

To obtain information about the locations were database files are, disk, mountpoint and to display the capacity, free space and space occupied on that drive by the database files

My way of achieving it

Using xp_cmdshell and powershell. The reason behind this was the fact that although xp_fixeddrives is great sometimes we need more information. This script is nothing more than an attempt to create something that can be used when we have to answer questions like

  • where my files for my databases are stored
  • the location is a mount point or not
  • what is the size of the drive where my database files are
  • how much in percentage the database files occupies from that drive

The output of the script looks like below

dltompandsizes

For me this type of information is useful when we have to show to other teams that actually the SQL Server is not the major consumer of that location or when we have to identify the disk number where the database files are in case we have to ask the storage team performance logs for it.

The script can be downloaded from here

Leave a Reply

Your email address will not be published. Required fields are marked *