Used and free space in data files

Question

Post reply

Used and free space in data files

Mark Shvarts 1

SSCertifiable

Points: 5702
More actions
November 21, 2008 at 9:04 am

#127895

How to programmatically find out how much space is available and how much is used for each file in a database ? sys.sysfiles provides only total space.
Basically, what I want to find is same as in SSMS Databases/database/Tasks/Shrink/Files
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Try this.

Select Name FileNme, size * 8/1024.0 Size_MB, fileproperty(Name,'SpaceUsed') * 8/1024.0 SpaceUsed_MB from sysfiles

SQL_Easy_btn? SSC Eights! Points: 930 More actions · Answer 2

Here is a really good thread on the topic.

http://www.sqlservercentral.com/Forums/Topic558227-146-1.aspx

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

SQL_Easy_btn? (11/21/2008)
Here is a really good thread on the topic.
http://www.sqlservercentral.com/Forums/Topic558227-146-1.aspx

The thread in that link seems to be about the amount of free space on the drives, not about the amount of free space in the database files.

The code on the link below provides a much more detailed analysis of your database files for all databases on a server:

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

Here is a simple answer:

select

[FileSizeMB]=

convert(numeric(10,2),round(a.size/128.,2)),

[UsedSpaceMB]=

convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,

[UnusedSpaceMB]=

convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,

[DBFileName]= a.name

from

sysfiles a

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 4

Don't forget about DBCC SHOWFILESTATS. It works on a single database at a time, and only shows data file info.

For log file info there's also DBCC SQLPERF(LOGSPACE) which shows log file information for all databases.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

Not my idea, but... find's files for current database and makes a report of used, unused, and total size...

select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)),

[UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,

[UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,

[DBFileName] = a.name

from sysfiles a

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Jeff Moden (11/23/2008)
Not my idea, but... find's files for current database and makes a report of used, unused, and total size...
select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from sysfiles a

Other than formatting, isn't that exactly the same code I posted two messages before? :ermm:

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 7

Here's a script I wrote a while back to make sp_spaceused a bit more table friendly.

http://www.sqlservercentral.com/scripts/sp_spaceused/64271/[/url]

use YOUR_DATABASE_HERE -- change as needed

set nocount on

declare @dbsize bigint,

@logsize bigint,

@reservedpages bigint,

@usedpages bigint,

@pages bigint

select @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end)),

@logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end))

from dbo.sysfiles

select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages),

@pages = sum(

case

when it.internal_type IN (202, 204) then 0

when a.type <> 1 then a.used_pages

when p.index_id < 2 then a.data_pages

else 0

end

)

from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id

left join sys.internal_tables it on p.object_id = it.object_id

select db_name() "Database Name",

cast(((@dbsize + @logsize) * 8192/1048576.) as decimal(15, 2)) "DB Size(MB)",

(case when @dbsize >= @reservedpages then cast(((@dbsize - @reservedpages) * 8192/1048567.) as decimal(15, 2)) else 0 end) "Unalloc. Space(MB)",

cast((@reservedpages * 8192/1048576.) as decimal(15, 2)) "Reserved(MB)",

cast((@pages * 8192/1048576.) as decimal(15, 2)) "Data Used(MB)",

cast(((@usedpages - @pages) * 8192/1048576.) as decimal(15, 2)) "Index Used(MB)",

cast(((@reservedpages - @usedpages) * 8192/1048576.) as decimal(15, 2)) "Unused(MB)"

go

Something you could do is run the script above on multiple databases (either with sp_msforeachdb or your own list for certain tables) and store it into a general system monitoring table.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein