SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Free space in DB file.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  10:26:52  Show Profile  Reply with Quote
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 - 04/19/2007 :  10:32:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  10:44:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/19/2007 :  11:09:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  11:39:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/19/2007 :  11:59:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/19/2007 :  12:13:32  Show Profile  Visit dinakar's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  12:51:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/19/2007 :  12:52:15  Show Profile  Reply with Quote
Yeah - the parameter you used is for the object (table).

Glad to help
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  12:55:30  Show Profile  Reply with Quote
Thnaks anyway!

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

mcrowley
Aged Yak Warrior

771 Posts

Posted - 04/19/2007 :  14:13:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  16:08:14  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 04/19/2007 :  19:29:37  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 04/19/2007 20:49:58
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  19:44:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  19:49:18  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 04/19/2007 :  20:48:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 04/19/2007 :  20:58:46  Show Profile  Reply with Quote
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

Australia
1 Posts

Posted - 04/13/2010 :  19:17:57  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 06/06/2012 :  08:20:34  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 06/06/2012 :  08:24:54  Show Profile  Reply with Quote
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

Canada
1 Posts

Posted - 05/27/2013 :  11:18:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000