How do I see total disk space in SQL Server?

How do I see total disk space in SQL Server?

Get a list of databases file with size and free space for a database in SQL Server:

1. SELECT DB_NAME() AS DbName,
2. name AS FileName,
3. size/128.0 AS CurrentSizeMB,
4. size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS INT)/128.0 AS FreeSpaceMB.
5. FROM sys. database_files.
6. WHERE type IN (0,1);

How do I check SQL free space?

Ways to find the free space:

1. Use sp_spaceused to check free space in SQL Server USE Solivia. GO. sp_spaceused.
2. Use DBCC SQLPERF to check free space in SQL Server Database USE Solivia. GO.
3. Use DBCC SHRINKFILE to determine free space in SQL log file USE Solivia. GO.
4. Use FILEPROPERTY to find free space in a database.

How to check free disk space for SQL Server?

Often we face the situation where we need to check the total disk space and available disk space for both physical and LUN/Mount drives. The extended stored procedure xp_fixeddrives is unable to help us in this scenario, so we have to log into the machine to check the total and free disk space for each physical/LUN/mount drive.

Where to find space usage in SQL Server?

Logon to the SQL Server Instance [SQL Server 2005 or SQL Server 2008] as shown below. [Refer Fig 1.0] Fig 1.0 Navigate to the database that you want to get the space information on, as shown below. [Refer Fig 1.1]

How to find the disk size of SQL Server?

Since you mentioned you are already using xp_fixeddrives ,following code will give you total size. Many shops will not allow this for security reason. I copied the code from here.

How can I check disk space in PowerShell?

We can check the disk space by executing a PowerShell script using xp_cmdshell from SSMS. In order to do this, you need to make sure that xp_cmdshell is enabled on the SQL instance. You can execute the below script to check and enable xp_cmdshell.