We are using 6 databases in total for an application, and we can only share 4TB of space for all 6 databases (via SAN storage). I'd like to write a query (report) for a single database indicating the the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio. Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left. Are these fields that can be accessed via T-SQL query? If so, what would the query look like?
11/30/2012 - 5:13pm update:
note: it says "HIMC_RDC_LZ.mdf" and "HIMC_RDC_LZ.log" ... our database was renamed, hence the inconsistency
This query (
EXEC sp_spaceused ) gives two result sets. the first result set matches the "Shrink" screen shot.
Output from the Shrink dialog:
Here's the output from Aaron's answer:
Here's the output from Mike's answer: