Is there another way to create a script that I can see all database files with their disk space details across databases and across servers?

I can do it through Registered Servers.

But I want to know if I can do it using 1 instance with linked servers for all the servers. My idea is to generate a script that will read each database for each linked server then when I have all the data, it can now be passed to the SQL Server Job.


2 Answers 2


Powershell is the way. And it make it easier, just install dbatools on one of your admin servers and use get-dbadiskspace

Get-DbaDiskSpace -ComputerName server1, server2, server3 -Unit GB

if you have list of servers then you can use

$servers = get-content "D:\serverlist.txt"
foreach ($server in $servers) {
  Get-DbaDiskSpace -ComputerName $server -Unit GB

You can even dump the above info using invoke-sqlcmd2 into a central db for reporting .


You can get your list of linked servers from the sys.servers view. Then you can use a cursor to go through the list and generate dynamic sql to execute against each server.

Below is an example retrieving multiple database properties from all linked servers.

declare @servername varchar(255)
declare @sql nvarchar(4000)

DECLARE c_databases CURSOR 
SELECT  name FROM  sys.servers where is_linked = 1
-- Open the cursor.
OPEN c_databases 
-- Loop through the partitions.
WHILE (1=1) 
        FETCH NEXT FROM c_databases        
        INTO @servername

        IF @@FETCH_STATUS < 0 

                set @sql = N'
                select d.database_id,d.name as databasename,sp.name as owner,d.compatibility_level,cast(round(sum(mf.Size)/128,0) as float) as size,d.collation_name, d.user_access_desc,d.recovery_model_desc,d.is_read_only 
                From ['+@servername+'].master.sys.databases d
                inner join ['+@servername+'].master.sys.server_principals sp
                on d.owner_sid = sp.sid
                inner join ['+@servername+'].master.sys.master_files mf
                on d.database_id = mf.database_id
                where d.database_id <> 2
                group by d.database_id,d.name,sp.name,d.compatibility_level,d.collation_name, d.user_access_desc,d.recovery_model_desc,d.is_read_only,d.state'

                --print @sql
                EXEC sp_executesql @sql


CLOSE c_databases
DEALLOCATE c_databases 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.