Linked Server – Space Used by Database Files

linked-serverview

I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers).
I created view on client size, for all databases:

CREATE VIEW [dbo].[view] AS SELECT name AS 'DataFileName', 
   physical_name AS 'DataPhysicalName',
   ROUND(size / 128, 1) AS 'DataTotalSizeInMB', 
   ROUND(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0, 1) AS 
   'DataSpaceUsedInMB', 
   ROUND(size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0, 1) AS 
   'DataAvailableSpaceInMB' FROM  [sys].[database_files] WHERE type = 0

and on server side I used loop to query this view. But column "DataSpaceUsedInMB" works only for default database for my login.

Best Answer

@cicik, if you are looping through the databases at your local server like you mentioned, assuming you already have the list of remote databases stored somewhere in a local table, you could loop through the databases (e.g. cursor) and execute,

SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];

This should put the remote database context at the database you specified, and hence it works for FILEPROPERTY function and sys.database_files dmv that return values only for the current database.

Also, you might want to consider extracting the list of databases on the remote server and run the script on each execution at the remote server (client side) so you won't have to create the view on each database, and won't miss any new databases created without your knowledge.

UPDATE If you want to use script to dynamically extract the data from the linked server without adding the view on every database,

DECLARE @remotecmd nvarchar(max);

SET @remotecmd = N'
DECLARE @db     sysname,
        @cmd    nvarchar(max);

CREATE TABLE #dbspace 
(
    DataFileName nvarchar(128),
    DataPhysicalName nvarchar(260),
    DataTotalSizeInMB decimal(17,6),
    DataSpaceUsedInMB decimal(17,6),
    DataAvailableSpaceInMB decimal(17,6)
);

DECLARE dbcur CURSOR
LOCAL FAST_FORWARD
FOR SELECT name
    FROM sys.databases
    WHERE [state] = 0;

OPEN dbcur;
FETCH NEXT FROM dbcur INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = N''USE '' + QUOTENAME(@db) + N''; '';
SET @cmd = @cmd + 
N''
INSERT INTO #dbspace
SELECT 
    name AS [DataFileName], 
    physical_name AS [DataPhysicalName],
    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB], 
    ROUND(CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataSpaceUsedInMB], 
    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB] 
FROM  [sys].[database_files] WHERE type = 0'';

EXEC (@cmd);

FETCH NEXT FROM dbcur INTO @db;
END

CLOSE dbcur;
DEALLOCATE dbcur;

SELECT 
    DataFileName,
    DataPhysicalName,
    DataTotalSizeInMB,
    DataSpaceUsedInMB,
    DataAvailableSpaceInMB
FROM #dbspace;

DROP TABLE #dbspace;'

EXEC (@remotecmd) AT [YourLinkedServer];

Make sure your login has the access to all the databases on the remote server.

Related Question