0

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.

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 .

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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