Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How full is my datafile? Expand / Collapse
Author
Message
Posted Friday, October 12, 2012 6:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:24 AM
Points: 323, Visits: 532
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
Post #1372082
Posted Friday, October 12, 2012 7:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1372119
Posted Friday, October 12, 2012 8:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:32 AM
Points: 6,463, Visits: 13,914
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

select			df.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"
Post #1372148
Posted Monday, October 15, 2012 6:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:24 AM
Points: 323, Visits: 532
Hi there!

Thank you very much! That was exactly what I was looking for!

Kind regards,

Kev
Post #1372698
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse