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.
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 AsOfDatefrom 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 MVPhttp://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 explanationFILEPROPERTY (Transact-SQL)SQL Server 2012 Other Versions This topic has not yet been rated - Rate this topicReturns 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|