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).
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page

pootle_flump

1064 Posts

Posted - 2007-04-19 : 12:52:15
Yeah - the parameter you used is for the object (table).

Glad to help
Go to Top of Page

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-04-19 : 12:55:30
Thnaks anyway!

------------------------
I think, therefore I am - Rene Descartes
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
'
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -