Retrieve Database Size from Linked Server

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 2008 Forums

Transact-SQL (2008)

Retrieve Database Size from Linked Server

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-01-02 : 22:56:42

Hi all,

I'm trying to retrieve the size of a database that resides on another server - via a Linked server.

Select fileid,case when groupid = 0 then 'log file' else 'data file' end as FileType,
[file_size] = convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =convert(int,round(filepropertysysfiles.name,'SpaceUsed')/128.000,0)),
[space_left] =convert(int,round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,0)),
name as LogicalFile, filename as FilePath,
GETDATE() as AsOfDate
from ServerA.DatabaseB.dbo.sysfiles;

My problem is that the values returned for space_used and
space_left are null.

File_size returns a good value.

Why am I getting nulls for space_used and space_left?

Thanks, Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-03 : 01:19:59

sysfiles is in sys schema. is the above table user defined one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-01-03 : 17:58:43

The statement itself is bad. Run it locally and make sure it's functioning before attempting to use it on the remove server.

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2013-01-03 : 19:23:55

Scott and visakh16,

My post had a mistype. The space_used line should be...
[space_used] =convert(int,round(fileproperty(sysfiles.name,'SpaceUsed'()/128.000,0)),

It *does* work correctly when ran against the local server.

I don't know why it returns nulls for space_used and space_left when I run it against a remote server. Because I'm retrieving sizes from several servers, I was hoping to be able to do that from a single server, rather than the reverse.

Thanks for your interest. Do you have any ideas?
Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 02:58:19

this is from books online explanation

FILEPROPERTY (Transact-SQL)

SQL Server 2012 Other Versions This topic has not yet been rated - Rate this topic
Returns the specified file name property value when a file name in the current database and a property name are specified. Returns NULL for files that are not in the current database.

Hope this explains NULL value that you're seeing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources