Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have got 4 different servers and I want to get the free diskspace of all those in a single output.
using xp_fixeddrives we will get the free space in each drive .

Its not possible to get into all servers and check it as above.So I am trying to execute all it a once.

for example the result set of one server is as follows

drive   free_mb
C       1816
D       2553
E       2882
F       6247
G       16936
H       7281
I       2395

As I am a starter to sql please do help me to sort out this issue .

share|improve this question

3 Answers 3

Something like this should do it:

create table #Tmp
(
  Drive char(1),
  MBFree int,
  ServerName varchar(50)
)

insert into #Tmp(Drive, MBFree) exec Server1.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server1' where ServerName is null

insert into #Tmp(Drive, MBFree) exec Server2.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server2' where ServerName is null

select *
from #Tmp
order by ServerName, Drive

drop table #Tmp

Result:

Drive MBFree      ServerName
----- ----------- --------------------------------------------------
C     57859       Server1
D     384636      Server1
F     82353       Server1
C     4963        Server2
E     10605       Server2

Server1 and Server2 has to be Linked Servers in the server where you are executing the above statements.

share|improve this answer
    
Thanks Mikael...!!! Will try it . –  Anto Oct 5 '12 at 8:00
SELECT [Server] = 'Server1', Drive, [MB free] 
  FROM OPENQUERY([Server1], 'SET FMTONLY OFF;EXEC xp_fixeddrives')
UNION ALL
SELECT [Server] = 'Server2', Drive, [MB free]  
  FROM OPENQUERY([Server2], 'SET FMTONLY OFF;EXEC xp_fixeddrives');
share|improve this answer

If all you want to do is inspect the results of a query that runs on multiple servers (as opposed to doing something with the combined results), you can use either Registered Servers, or set up a Central Management Server (CMS). (This is something I recommend doing anyways if you manage more than one server.)

These facilities allow you not only to run queries against groups of servers, but also to use Policy-Based Management to validate and maintain server and database configurations (depending on your environment).


If you do need further processing of the combined results, you'll have to use another solution -- the methods in the other answers will certainly work. Depending on what you need to accomplish, you could also leverage things like SSIS, PowerShell, SQLCMD, etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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