Free space in DB file. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Free space in DB file.

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 10:26:52

How to check the free space in a DB file using TSQL?

------------------------
I think, therefore I am - Rene Descartes

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-04-19 : 10:32:41

You can use the FILEPROPERTY system function, or the sp_spaceused system procedure. sp_spaceused gives only an estimate (at least it did through SQL 2000. That may have changed).

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 10:44:35

Thank you mcrowley!

1. File property gives the total space, not the FREE SPACE in a file.
2. didn't work sp_spaceused for file

------------------------
I think, therefore I am - Rene Descartes

pootle_flump

1064 Posts

Posted - 2007-04-19 : 11:09:59

quote:
Originally posted by ravilobo

2. didn't work sp_spaceused for file

Could you expand on that?

Undocumented DBCC command - treat with caution:

DBCC showfilestats

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 11:39:12

DBCC showfilestats
-- Doesn't gives me the free space

sp_spaceused 'DB_file_Name'

Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 'DB_file_Name' does not exist in database 'DB_file_Name' or is invalid for this operation.

------------------------
I think, therefore I am - Rene Descartes

pootle_flump

1064 Posts

Posted - 2007-04-19 : 11:59:43

quote:
Originally posted by ravilobo

DBCC showfilestats
-- Doesn't gives me the free space

Come on - it tells you the size and the used space. I think therefore I am....

exec MyDbName.dbo.sp_spaceused

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-19 : 12:13:32

Here's some code I stole from somewhere on the net:
----------------

BEGIN

SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo'))
BEGIN
DROP TABLE #DBFileInfo
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats'))
BEGIN
DROP TABLE #LogSizeStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats'))
BEGIN
DROP TABLE #DataFileStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives'))
BEGIN
DROP TABLE #FixedDrives
END

CREATE TABLE #FixedDrives
(DriveLetter VARCHAR(10),
MB_Free DEC(20,2))

CREATE TABLE #DataFileStats
(DBName VARCHAR(255),
DBId INT,
FileId TINYINT,
[FileGroup] TINYINT,
TotalExtents DEC(20,2),
UsedExtents DEC(20,2),
[Name] VARCHAR(255),
[FileName] VARCHAR(400))

CREATE TABLE #LogSizeStats
(DBName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED,
DBId INT,
LogFile REAL,
LogFileUsed REAL,
Status BIT)

CREATE TABLE #DBFileInfo
([ServerName] VARCHAR(255),
[DBName] VARCHAR(65),
[LogicalFileName] VARCHAR(400),
[UsageType] VARCHAR (30),
[Size_MB] DEC(20,2),
[SpaceUsed_MB] DEC(20,2),
[MaxSize_MB] DEC(20,2),
[NextAllocation_MB] DEC(20,2),
[GrowthType] VARCHAR(65),
[FileId] SMALLINT,
[GroupId] SMALLINT,
[PhysicalFileName] VARCHAR(400),
[DateChecked] DATETIME)

DECLARE @SQLString VARCHAR(3000)
DECLARE @MinId INT
DECLARE @MaxId INT
DECLARE @DBName VARCHAR(255)

DECLARE @tblDBName TABLE
(RowId INT IDENTITY(1,1),
DBName VARCHAR(255),
DBId INT)

INSERT INTO @tblDBName (DBName,DBId)
SELECT [Name],DBId FROM Master..sysdatabases WHERE [name] = 'FASTPROD'
--(Status & 512) = 0 /*NOT IN (536,528,540,2584,1536,512,4194841)*/ ORDER BY [Name]

INSERT INTO #LogSizeStats (DBName,LogFile,LogFileUsed,Status)
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')

UPDATE #LogSizeStats
SET DBId = DB_ID(DBName)

INSERT INTO #FixedDrives EXEC Master..XP_FixedDrives

SELECT @MinId = MIN(RowId),
@MaxId = MAX(RowId)
FROM @tblDBName

WHILE (@MinId <= @MaxId)
BEGIN
SELECT @DBName = [DBName]
FROM @tblDBName
WHERE RowId = @MinId

SELECT @SQLString =
'SELECT ServerName = @@SERVERNAME,'+
' DBName = '''+@DBName+''','+
' LogicalFileName = [name],'+
' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,'+
' Size_MB = [size]*8/1024.00,'+
' SpaceUsed_MB = NULL,'+
' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize*8/1024.00 END,'+
' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'+
' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'+
' FileId = [fileid],'+
' GroupId = [groupid],'+
' PhysicalFileName= [filename],'+
' CurTimeStamp = GETDATE()'+
'FROM '+@DBName+'..sysfiles'

PRINT @SQLString
INSERT INTO #DBFileInfo
EXEC (@SQLString)

UPDATE #DBFileInfo
SET SpaceUsed_MB = (SELECT LogFileUsed FROM #LogSizeStats WHERE DBName = @DBName)
WHERE UsageType = 'Log'
AND DBName = @DBName

SELECT @SQLString = 'USE ' + @DBName + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'

INSERT #DataFileStats (FileId,[FileGroup],TotalExtents,UsedExtents,[Name],[FileName])
EXECUTE(@SQLString)

UPDATE #DBFileInfo
SET [SpaceUsed_MB] = S.[UsedExtents]*64/1024.00
FROM #DBFileInfo AS F
INNER JOIN #DataFileStats AS S
ON F.[FileId] = S.[FileId]
AND F.[GroupId] = S.[FileGroup]
AND F.[DBName] = @DBName

TRUNCATE TABLE #DataFileStats

SELECT @MinId = @MInId + 1
END

SELECT [ServerName],
[DBName],
[LogicalFileName],
[UsageType] AS SegmentName,
B.MB_Free AS FreeSpaceInDrive,
[Size_MB],
[SpaceUsed_MB],
[MaxSize_MB],
[NextAllocation_MB],
CASE MaxSize_MB WHEN -1 THEN CAST(CAST(([NextAllocation_MB]/[Size_MB])*100 AS INT) AS VARCHAR(10))+' %' ELSE 'Pages' END AS [GrowthType],
[FileId],
[GroupId],
[PhysicalFileName],
[DateChecked]
FROM #DBFileInfo AS A
LEFT JOIN #FixedDrives AS B
ON SUBSTRING(A.PhysicalFileName,1,1) = B.DriveLetter
ORDER BY DBName,GroupId,FileId

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo'))
BEGIN
DROP TABLE #DBFileInfo
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats'))
BEGIN
DROP TABLE #LogSizeStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats'))
BEGIN
DROP TABLE #DataFileStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives'))
BEGIN
DROP TABLE #FixedDrives
END

END

GO

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 12:51:28

quote:
Originally posted by pootle_flump
exec MyDbName.dbo.sp_spaceused

Thank you. I was using it in a diffrent way ;-)

------------------------
I think, therefore I am - Rene Descartes

pootle_flump

1064 Posts

Posted - 2007-04-19 : 12:52:15

Yeah - the parameter you used is for the object (table).

Glad to help

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 12:55:30

Thnaks anyway!

------------------------
I think, therefore I am - Rene Descartes

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-04-19 : 14:13:39

quote:
Originally posted by ravilobo

Thank you mcrowley!

1. File property gives the total space, not the FREE SPACE in a file.
2. didn't work sp_spaceused for file

------------------------
I think, therefore I am - Rene Descartes

Have a look at the FILEPROPERTY ( file_name , property )
function in BOL. I do not believe it gives you total space at all.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 16:08:14

Thats right! Today is a bad day..;-)

------------------------
I think, therefore I am - Rene Descartes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-19 : 19:29:37

This should do what you want:


select
	a.FILEID,
	[FILE_SIZE_MB] = 
		convert(decimal(12,2),round(a.size/128.000,2)),
	[SPACE_USED_MB] =
		convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
		convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
	NAME = left(a.NAME,15),
	FILENAME = left(a.FILENAME,30)
from
	dbo.sysfiles a


Results:

FILEID FILE_SIZE_MB   SPACE_USED_MB  FREE_SPACE_MB  NAME            FILENAME                       
------ -------------- -------------- -------------- --------------- ------------------------------ 
1      2.94           2.88           .06            Northwind       D:\MSSQL\DATA\northwnd.mdf   
2      1.00           .46            .54            Northwind_log   D:\MSSQL\DATA\northwnd.ldf   

(2 row(s) affected)

Edit: Changed to show 2 decimal places and simplify calculation of free space.

CODO ERGO SUM

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 19:44:47

Thank you MVJ! Thats great!

I am trying to understand how it works.

------------------------
I think, therefore I am - Rene Descartes

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 19:49:18

Why you are dividing by 128 and not by 1024?

------------------------
I think, therefore I am - Rene Descartes

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-19 : 20:48:24

quote:
Originally posted by ravilobo

Why you are dividing by 128 and not by 1024?...

select
	DB_PAGES_PER_MEGABYTE = BYTES_PER_MEGABYTE/BYTES_PER_DB_PAGE ,
	*
from
	(
	select
		BYTES_PER_MEGABYTE	= 1024*1024 ,
		BYTES_PER_DB_PAGE	= 1024*8
	) a



Results:

DB_PAGES_PER_MEGABYTE BYTES_PER_MEGABYTE BYTES_PER_DB_PAGE 
--------------------- ------------------ ----------------- 
128                   1048576            8192

(1 row(s) affected)

CODO ERGO SUM

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 20:58:46

Got it size is in 8KB pages!!!! Never knew this....

------------------------
I think, therefore I am - Rene Descartes

Jagadeshs
Starting Member

1 Post

Posted - 2010-04-13 : 19:17:57

Hi Michael Valentine Jones

It is a excellet Query that you Posted on 04/19/2007 at 19:29:37.

Cheers,
Jagi.

k3nnyg
Starting Member

3 Posts

Posted - 2012-06-06 : 08:20:34

Here is the above query modified to give you the free space for each DB on the server. (Note that sp_msforeachdb is an undocumented command)Well done on the query btw :)

Exec sp_msforeachdb '
use

select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a
'

k3nnyg
Starting Member

3 Posts

Posted - 2012-06-06 : 08:24:54

In the post above in front of "use" should be [ ? ] (Left bracket, Question mark, right bracket)no spaces. I dont know if it shows up differently for others but for me it just shows up as a circle with a question mark in it.

airbiker
Starting Member

1 Post

Posted - 2013-05-27 : 11:18:38

I really like that thread and find it usefull.

Here's my little improvement:


use master
go

select 
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30),
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) 
into #temp_db_free_space
from
dbo.sysfiles a
where 0=1


Exec sp_msforeachdb '
use 

insert into  #temp_db_free_space
select
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30),
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) 
from
dbo.sysfiles a
'
select * from  #temp_db_free_space
order by 1, 2
drop table #temp_db_free_space

(replace question mark by left-bracket question-mark right-bracket)

This will give you a nice list where you can sort it afterward.

Guy

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources