Database Files through Linked Servers

Question

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.

Thanks.

Answer 1

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 .

Answer 2

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 
FOR 
SELECT name FROM sys.servers where is_linked = 1
-- Open the cursor.
OPEN c_databases 
-- Loop through the partitions.
WHILE (1=1) 
BEGIN 
 FETCH NEXT FROM c_databases 
 INTO @servername
 IF @@FETCH_STATUS < 0 
 BREAK 
 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
END
CLOSE c_databases
DEALLOCATE c_databases 
GO