Space used by databases file through linked server

Question

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.

• Travis: how do you loop through the view?
• drizzt: I created view on all databases and then loop on central server through name of databases like SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];, but it not work

Answer 1

@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.

• drizzt: no, I tried this but it not work
• Travis: @Cicik I tested on my test machine and it works even for a login with public role and select permission on dbo.[view]. What is it that doesn't work? Does it returns any result?
• Travis: @Cicik I have added some script to extract the information dynamically without having to create the view in every database.
• drizzt: Really thanks for advice, it help me solve my problem.
• Edgar Allan Bayron: May I know where does the #dbspace stored? Is it on the server where you ran it or in the server where you executed the dynamic query?

Answer 2

From BOL on FILEPROPERTY: "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."

You will have to run your query under the context of each database in order to find the SpaceUsed. You could do something like this:

First create a table to store the data (or a temp table if you'd like):

IF EXISTS (
 SELECT *
 FROM sys.tables
 WHERE NAME LIKE '%dbsize%'
 )
DROP TABLE dbsize
CREATE TABLE dbsize (
 DataFileName VARCHAR(50)
,DataPhysicalName VARCHAR(500)
,DataTotalSizeInMB INT
,DataSpaceUsedInMB DECIMAL(30, 2)
,DataAvailableSpaceInMB DECIMAL(30, 2)
)
GO

Then you will have to populate this table with the data (I'm going to use sp_msforeachdb for simplicity. You could also use a cursor).

INSERT INTO dbsize (
DataFileName
,DataPhysicalName
,DataTotalSizeInMB
,DataSpaceUsedInMB
,DataAvailableSpaceInMB
)
EXEC sp_msforeachdb 'use [?]; 
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'
SELECT *
FROM dbsize

Then you could repopulate the table how ever often you like. You should be able to query this table through a linked server connection to find the data you are looking for.

• Kin Shah: msforeachdb is UNRELIABLE and undocumented as it can skip databases. See Aaron Bertrand post sqlblog.org/2020/08/04/…
• drizzt: see comment above, problem is linked server.

Answer 3

But column "DataSpaceUsedInMB" works only for default database for my login.

You have to use : sys.master_files :

Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

You are using sys.database_files :

Contains a row per file of a database as stored in the database itself. This is a per-database view.

So, SELECT * FROM sys.master_files will list the files for each database in the instance whereas SELECT * FROM sys.database_files will list the files for the specific database context.

Note: If you're not seeing rows from sys.master_files, it could be a permissions issue as BOL states:

The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

Whereas for sys.database_files just requires membership in the public role.

Update : As highlighted, you can use your existing script with a way to loop all the databases and store that result in a table and then create a view (if you want).

You can use below script :

DECLARE
 @SqlStatement nvarchar(MAX)
 ,@DatabaseName sysname;
CREATE TABLE DatabaseSpace(
 DATABASE_NAME sysname
 ,LOGICAL_NAME sysname
 ,FILE_SIZE_MB decimal(12, 2)
 ,SPACE_USED_MB decimal(12, 2)
 ,FREE_SPACE_MB decimal(12, 2)
 ,FILE_NAME sysname
 );
DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
 SELECT name FROM sys.databases;
OPEN DatabaseList;
WHILE 1 = 1
BEGIN
 FETCH NEXT FROM DatabaseList INTO @DatabaseName;
 IF @@FETCH_STATUS = -1 BREAK;
 SET @SqlStatement = N'USE '
 + QUOTENAME(@DatabaseName)
 + CHAR(13)+ CHAR(10)
 + N'INSERT INTO DatabaseSpace
 SELECT
 [DATABASE_NAME] = DB_NAME()
 ,[LOGICAL_NAME] = f.name
 ,[FILE_SIZE_MB] = CONVERT(decimal(12,2),round(f.size/128.000,2))
 ,[SPACE_USED_MB] = CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))
 ,[FREE_SPACE_MB] = CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))
 ,[FILENAME] = f.name
 FROM sys.database_files f;';
 EXECUTE(@SqlStatement);
END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;
SELECT * FROM DatabaseSpace;
--DROP TABLE DatabaseSpace;;
--GO

UPDATE (6/21/2013) :

this works on local, but NOT through LINKED SERVER, and that is core of my my problem. as @TravisGan mentioned problem is FILEPROPERTY function to find used space, because it works only for default database when connecting linked server. Anyway I have no idea how to execute this script through linked server. Is there any other way to find space in database file?

IF you want to execute query on multiple servers, then Powershell or SQLCMD is your best option.

Using linked servers, you have to use OPENROWSET or OPENQUERY.

IF you just have couple of servers then creating a linked server to the main will work fine as below :

Note : SQL2005 is a linked server.

 create view test_view as 
SELECT db.name AS DatabaseName,
 SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
 SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
 SUM(8192.0E * af.size / 1048576.0E) AS TotalSize
FROM SQL2005.master.dbo.sysdatabases AS db
INNER JOIN SQL2005.master.dbo.sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE db.name NOT IN('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') 
GROUP BY db.name
select * from test_view

enter image description here

• Travis: I initially thought that, but the problem is his use of FILEPROPERTY function that only return the current database. So it won't work even to replace with sys.master_files
• Kin Shah: @TravisGan the OP can use a while loop or undocumented ms_foreachDB or Aarons version of that as well.
• Travis: Right, if he is looping the database that way, sys.database_files should work as well.
• Kin Shah: @TravisGan Updated the my answer with sys.database_files + a loop and cursor. Thanks
• drizzt: this works on local, but NOT through LINKED SERVER, and that is core of my my problem. as @TravisGan mentioned problem is FILEPROPERTY function to find used space, because it works only for default database when connecting linked server. Anyway I have no idea how to execute this script through linked server. Is there any other way to find space in database file?