Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006

    Unanswered: obtaining disk space

    I'm new to these forums (and to SQL Server), so please be gentle with me.

    I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
    I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.

    I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.

    I am having difficulty obtaining the free drive space details.
    I'm using :-
    'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)

    I wish to avoid creating any objects on the remote servers if at all possible.
    I really want to pass the remote server name into the procedure, and the output to be inserted into the table.

    Many thanks

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    set nocount on

    create table #FreeSpace(Drive char(1), MB_Free int)

    insert into #FreeSpace exec master..xp_fixeddrives

    drop table #FreeSpace
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Mar 2006
    Thanks for the reply Blindman, I think the issue is that the insert is being turned into a distributed transaction when the "xp_fixeddisks" function is being called eg...

    "insert into my_local_database.dbo.disk_space (drive,mb_free)
    exec the_remote_server.master.dbo.xp_fixeddrives"

    returns the error "MSDTC on server 'my_local_server' is unavailable".
    Whereas the sql to get the file size stats, uses pure sql rather than the exec and works :-

    "insert into my_local_database.dbo.db_growth select 'the_remote_server','northwind', getdate() as timestamps, *
    from the_remote_server.northwind.dbo.sysfiles"

    If it is run locally, the insert works :-
    "insert into my_local_database.dbo.disk_space (drive,mb_free) exec master.dbo.xp_fixeddrives"

    it is the "exec the_remote_server......." bit that is giving me problems.

    I could get it to work by using two processes - (1) the remote server runs the function and inserts into a table of it's own, then (2) my_local_server grabs that data and inserts into the central table, but I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.

    Last edited by dbagriff; 03-24-06 at 06:00.

  4. #4
    Join Date
    Jun 2003
    Quote Originally Posted by dbagriff
    I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.

    No changes on remote server.
    create table FreeSpace
    Drive nvarchar(5), 
    MB_Free nvarchar(20)
    create Procedure remoteServerDiskSpace_sp ( 
     @servername nvarchar(4000), 
     @FileName varchar(50) 
    declare @bulksql varchar(200)
                SET @servername = 'osql -Slocalhost -E -q"SET NOCOUNT ON; exec ' + @servername +' .master.dbo.xp_fixeddrives" -s"," -n -w8000 -o"' + @FileName +'"' 
                Exec master..xp_cmdshell @servername, NO_OUTPUT 
          set @bulksql='BULK INSERT FreeSpace FROM '''+@FileName+''' WITH (FIELDTERMINATOR = '','',FirstRow=3)'
    -- execute query
    exec remoteServerDiskSpace_sp 'remoteserverName','c:\DiskSpace.csv'
    --select query
    select * from FreeSpace
    I love cursor,though I never use it.Cos' I dont want to misuse it.


  5. #5
    Join Date
    Mar 2006
    Thanks Mallier,
    I've now done something in a similar vein to your routine, and it works a treat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts