Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

enter image description here

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.

enter image description here

Output from the Shrink dialog:

enter image description here

Here's the output from Aaron's answer:

enter image description here

Here's the output from Mike's answer:

enter image description here

share|improve this question

3 Answers 3

up vote 8 down vote accepted

Here is the query Management Studio uses to populate those numbers:

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df 
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
SUM(a.total_pages) AS [SpaceUsed],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df 
   WHERE df.type in (1, 3)) AS [LogSize]
FROM
sys.partitions p join sys.allocation_units a 
  on p.partition_id = a.container_id 
left join sys.internal_tables it 
  on p.object_id = it.object_id

You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:

SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df 
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df 
   WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a 
  on p.partition_id = a.container_id;

With this update, assuming your database doesn't change in the meantime, it should yield:

753475.94    744030.07    2900.00

Doing some simple math, and isolating just the three numbers you want:

;WITH t(s) AS
(
  SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
   FROM sys.database_files
   WHERE [type] % 2 = 0
), 
d(s) AS
(
  SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
   FROM sys.partitions AS p
   INNER JOIN sys.allocation_units AS a 
   ON p.[partition_id] = a.container_id
)
SELECT 
  Allocated_Space = t.s, 
  Available_Space = t.s - d.s,
  [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
FROM t CROSS APPLY d;
share|improve this answer
    
see my update in my question for more details regarding this query you ran –  MacGyver Nov 30 '12 at 23:12
    
@MacGyver this is the query that is used by the dialog you're looking at to get the database size and space used (the available and % are just math). You'll need to repeat the query for each database since you need to access the database-specific DMVs in their own database context. –  Aaron Bertrand Nov 30 '12 at 23:15
    
I'm getting number discrepancies right now between what the Shrink dialog says and these queries, so I'm just trying to understand what's there first. –  MacGyver Nov 30 '12 at 23:24
1  
You need to perform math on the output, just like SSMS does. The output from the query it runs is expressed in 8K pages. So, for example: SELECT 96444920 * 8 / 1024.0 = 753475.937500. –  Aaron Bertrand Nov 30 '12 at 23:27
1  
96444920 is number of 8 KB PAGES. So, multiply 96444920 * 8 and you get the number of KB represented by those pages. Divide by 1024 to get number of MB. I was never trying to get you to TB. I was trying to show you how to get the same number you were showing us in those dialogs from Management Studio. You'll have to perform more or different math to get to TB. –  Aaron Bertrand Dec 4 '12 at 19:10

Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries(you would need to change the math for TBs):

SELECT f.name AS [File Name] 
, f.physical_name AS [Physical Name]
, CAST((f.size/128.0) AS decimal(10,2)) AS [Total Size in MB]
, CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(10,2)) AS [Available Space In MB]
, [file_id]
, fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);
share|improve this answer
    
Arithmetic overflow error converting numeric to data type numeric. –  MacGyver Nov 30 '12 at 22:08
    
Looks like the decimal(5,2) is to tight for a number. Updated the query to be decimal (10,2) to support larger numbers. –  Mike Fal Nov 30 '12 at 22:17

Here's some SQL that might work for you.


Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL, 
       filName varchar(520) NULL, sizeMg decimal(10,2) null, 
       usedMg decimal(10,2) null, freeMg decimal(10,2) null, 
       pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)
Declare @sSql varchar(1000)
Set @sSql = 'Use [?];
Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)
Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), 
Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))
From dbo.sysfiles Order By groupId Desc;'
Exec sp_MSforeachdb @sSql
Update #dbInfo Set
freeMg = sizeMg - usedMg,
pcntUsed = (usedMg/sizeMg)*100,
pcntFree = ((sizeMg-usedMg)/sizeMg)*100

select * from #dbInfo compute sum(sizeMG),  sum(FreeMg) 
drop table #dbInfo

It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.

share|improve this answer
    
Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'. –  Albert Widjaja Jul 23 at 0:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.