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

    Unanswered: obtaining disk space

    Hi,
    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
    Location
    Ohio
    Posts
    12,592
    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
    go
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2006
    Posts
    4
    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.

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

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    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.

    Thanks.
    No changes on remote server.
    Code:
    create table FreeSpace
    (
    Drive nvarchar(5), 
    MB_Free nvarchar(20)
    )
    go
    create Procedure remoteServerDiskSpace_sp ( 
     @servername nvarchar(4000), 
     @FileName varchar(50) 
    ) 
    AS 
    declare @bulksql varchar(200)
    Begin 
     
     
                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)'
          exec(@bulksql)
    END 
    GO
    -- 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.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Mar 2006
    Posts
    4
    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
  •