Alternative to FILEPROPERTY for used/unused space?

Question

Alternative to FILEPROPERTY for used/unused space?

Adam Bean

One Orange Chip

Points: 26631
More actions
July 2, 2008 at 12:53 pm

#154497

Hey all,
I created a view to help assist in showing all data and log file sizes (and some other stuff), but I'd like to be able to keep it as a view and not take it to a procedure. The reason I'd have to take it a procedure is to be within the context of the database to use the FILEPROPERTY function.
Any alternative to how I did this to retrieve the used/unused space?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Why would you have to make it a proc to use a function like FileProperty? Just set the right columns to be the input parameters, and use Cross Apply instead of Join. (Look up Cross Apply in Books Online.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

GSquared (7/2/2008)
Why would you have to make it a proc to use a function like FileProperty? Just set the right columns to be the input parameters, and use Cross Apply instead of Join. (Look up Cross Apply in Books Online.)

Maybe I'm missing something, but according to BOL:

Arguments

file_name

Is an expression that contains the name of the file associated with the current database for which to return property information. file_name is nchar(128).

property

Is an expression that contains the name of the file property to return. property is varchar(128), and can be one of the following values.

Meaning that I have to be in the context of the database.

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

You can use the script on this link to get information on all databases on a server at the same time.

Get Server Database File Information

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Michael Valentine Jones (7/2/2008)
You can use the script on this link to get information on all databases on a server at the same time.
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

Thanks, but not what I'm looking for.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Adam Bean

One Orange Chip

Points: 26631

July 3, 2008 at 6:23 am

#837770

Any assistance would be much appreciated.

Thanks

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Adam Bean

One Orange Chip

Points: 26631

July 7, 2008 at 9:23 am

#838990

Shameless bump ...

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Forgot about this ... back to it. Yeah I could turn it into a proc and cursor through and update a temp table, but I really want to keep this as a view. Anyone know of a different way to determine the used/unused without using FILEPROPERTY?

I'm gonna start on pulling out the unallocated from sp_spaceused ... perhaps I can use that.

Thanks

shannon714 SSC Journeyman Points: 81 More actions · Answer 8

Adam,

It's been a while so I hope you already found your answer. If not, Tim Ford had a good tip to put this at the beginning of your foreachdb command - 'Use [' + '?' + '] ...'

http://www.mssqltips.com/tip.asp?tip=1426

Cheers

Shannon

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

shannon714 (5/6/2010)
Adam,
It's been a while so I hope you already found your answer. If not, Tim Ford had a good tip to put this at the beginning of your foreachdb command - 'Use [' + '?' + '] ...'
http://www.mssqltips.com/tip.asp?tip=1426
Cheers
Shannon

Thanks shannon, but not something I like to do. I was able to do what I needed, just had to resort to dynamic SQL and make a procedure.

Neeraj Mittal Mr or Mrs. 500 Points: 536 More actions · Answer 10

You can use DBCC SHOWFILESTATS WITH NO_INFOMSGS. It will provide you the file_id_, file_group, total_extents, used_extents, File name, file_Location)

Thanks

Neeraj

marmik.csi SSC Enthusiast Points: 124 More actions · Answer 11

So, SP_spaceUsed worked for you?

I am working on the similar where I am using this(below) which works fine except (tempdb-->templog [log file])

So, I am also looking for alternate to eliminate the exception. 🙂

SELECT

FILEPROPERTY(DF.name, 'SpaceUsed')/128.0

FROM sys.databases D WITH(NOLOCK)

JOIN sys.master_files DF WITH(NOLOCK)

ON D.database_id = DF.database_id