| Author |
 Topic  |
|
|
jbates99
Constraint Violating Yak Guru
285 Posts |
 Posted - 01/02/2013 : 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
India
47023 Posts |
Posted - 01/03/2013 : 01:19:59
|
sysfiles is in sys schema. is the above table user defined one?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
ScottPletcher
Yak Posting Veteran
USA
78 Posts |
Posted - 01/03/2013 : 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
285 Posts |
Posted - 01/03/2013 : 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
India
47023 Posts |
Posted - 01/04/2013 : 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/
|
|
|
| |
Topic |
|
Retrieve Database Size from Linked Server
