October 12, 2012 at 6:47 am
Hi folks!
When I create my database I define sizes for the datafiles that make it up, by default the MDF and LDF datafiles. I might specify 250MB for the MDF and 50MB for the LDF. These sizes are the sizes that are displayed when I look in Windows Explorer, run sp_helpdb and so on.
The problem is this: How do I find out how much of that datafile is actually filled with data and how much is empty and reserved for use using SQL-based queries?
Many thanks for your help!
Regards,
Kev
October 12, 2012 at 7:31 am
This is the one i use.
use master
CREATE TABLE #TMPFIXEDDRIVES (
DRIVE CHAR(1),
MBFREE INT)
INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES
CREATE TABLE #TMPSPACEUSED (
DBNAME VARCHAR(50),
FILENME VARCHAR(50),
SPACEUSED FLOAT)
INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')
SELECT C.DRIVE,
CASE
WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS DISKSPACEFREE,
A.NAME AS DATABASENAME,
B.NAME AS FILENAME,
CASE B.TYPE
WHEN 0 THEN 'DATA'
ELSE TYPE_DESC
END AS FILETYPE,
CASE
WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS FILESIZE,
CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
B.PHYSICAL_NAME
FROM SYS.DATABASES A
JOIN SYS.MASTER_FILES B
ON A.DATABASE_ID = B.DATABASE_ID
JOIN #TMPFIXEDDRIVES C
ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
JOIN #TMPSPACEUSED D
ON A.NAME = D.DBNAME
AND B.NAME = D.FILENME
ORDER BY DATABASENAME asc
DROP TABLE #TMPFIXEDDRIVES
DROP TABLE #TMPSPACEUSED
Regards
Durai Nagarajan
October 12, 2012 at 8:05 am
kevaburg (10/12/2012)
How do I find out how much of that datafile is actually filled with data and how much is empty and reserved for use using SQL-based queries
Run this per database
selectdf.name AS LogicalFileName
, isnull(fg.name, 'Log') AS FilegroupName
, df.physical_name AS PhysicalOSName
, (df.size * 8 / 1024) AS SizeMBs
, (fileproperty(df.name, 'SpaceUsed') / 128) AS SpaceUsedMBs
, (df.size * 8 / 1024) - (fileproperty(df.name, 'SpaceUsed') / 128) AS FreeMBs
, case df.max_size
when 0 then 'No Growth'
when -1 then 'Unlimited'
when 268435456 then '2TB'
else cast(df.max_size / 128 AS VARCHAR(10)) + ' MBs'
end AS MaxFileSize
, case df.is_percent_growth
when 0 then cast(df.growth / 128 AS VARCHAR(10)) + ' MBs'
else CAST(df.growth AS VARCHAR(10)) + ' %'
end AS Growth
,cast(cast((fileproperty(df.name, 'SpaceUsed') / 128) as numeric(20,2)) /
cast(df.size / 128 as numeric(20,2))
* 100 as numeric(20,2)) as PercentUsed
from sys.database_files df left outer join sys.filegroups fg ON df.data_space_id = fg.data_space_id
order by df.type
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 15, 2012 at 6:47 am
Hi there!
Thank you very much! That was exactly what I was looking for!
Kind regards,
Kev:w00t:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply