Get Hard Disk information from linked servers

• mvdveer

  SSC Enthusiast

  Points: 167

  More actions

  November 15, 2011 at 2:35 am

  #390859

  Hi There,

  As a MS SQL Server 2005 DBA where I have to maintain 10 production SQL Servers 2005 I have setup a master server from where I have sp's running that collect information using linked server from the other MS SQL Servers 2005, and store it on the master server for early warning purposes and when I have an audit I can show the reports that I have created with that information.

  There is a sp that I built for getting the available hard disk space and save it in a table. I would like store also the total drive capacity drives of that server also using linked server.

  But I cannot find any solution that fits my wishes. Does anybody here has a suggestion how I can do it?

  Beneath here is the script that i want to improve.

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spCalculateDiskSpaceOnAllServers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

  drop procedure [dbo].[spCalculateDiskSpaceOnAllServers]

  GO

  CREATE PROCEDURE [dbo].[spCalculateDiskSpaceOnAllServers] AS

  SET NOCOUNT ON

  BEGIN

  -- 1 - Declaratie van de variables

  DECLARE@MBfreeint

  ,@MinMBFreeint

  ,@Drivechar(1)

  ,@sqlnvarchar(4000)

  ,@return_codeint

  ,@server_namesysname

  -- Initialisatie variables

  SET @MBfree= 0

  SET @MinMBFree= NULL

  -- CleanUp dbo.Remaining_HD_Space prfevent for loading double data

  DELETE FROM dbo.Remaining_HD_Space

  WHERE CONVERT(varchar(8), loaddate, 112) = CONVERT(varchar(8), getdate(), 112)

  OR datediff(d,convert(datetime,convert(varchar(8),loaddate,112),112),getdate()) > 60

  -- Create temp tables

  CREATE TABLE #tbl_xp_fixeddrives (Drive varchar(2) NOT NULL,

  [MB free] int NOT NULL )

  DECLARE servers_cursor CURSOR

  FOR

  SELECT srvname

  FROM master.dbo.sysservers

  WHERE srvproduct = 'SQL Server'

  ORDER BY srvname

  OPEN servers_cursor

  FETCH servers_cursor INTO @server_name

  -- Get data and fill it.

  WHILE @@fetch_status = 0

  BEGIN

  SET @sql = ''

  SET @sql = 'insert into #tbl_xp_fixeddrives SELECT * from openquery([' + @server_name + '], ''set fmtonly off;EXEC master.sys.xp_fixeddrives'')'

  EXEC sp_executesql @sql

  -- Insert into table

  INSERT INTO dbo.Remaining_HD_Space

  SELECTCONVERT(VARCHAR(10),GETDATE(),105) as datum

  ,DRIVEAS [Harde Schijf]

  ,[mb Free]AS [Vrije ruimte (MB)]

  ,(CAST([mb Free] AS FLOAT)/1000) AS [Vrije ruimte (GB)]

  ,@server_name AS server_name

  ,GETDATE()AS [loaddate]

  FROM #tbl_xp_fixeddrives

  TRUNCATE TABLE #tbl_xp_fixeddrives

  FETCH servers_cursor INTO @server_name

  END

  CLOSE servers_cursor

  DEALLOCATE servers_cursor

  -- 7 - DROP TABLE #tbl_xp_fixeddrives

  DROP TABLE #tbl_xp_fixeddrives

  END

  SET NOCOUNT OFF

  Thanks in advance

• arr.nagaraj

  SSCertifiable

  Points: 6528

  More actions

  November 15, 2011 at 2:48 am

  #1407684

  As far as I know, you cant get it unless you use dos commands using xp_cmdshell.

  Regards,
  Raj

  http://Strictlysql.blogspot.com

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  November 15, 2011 at 3:12 am

  #1407696

  Why don't you use Powershell?

  This should give you a start

  param ( [string]$ComputerName = "YourServer" )

  gwmi -query "SELECT SystemName,Caption,VolumeName,Size,Freespace FROM win32_logicaldisk WHERE DriveType=3" -computername "$ComputerName" | Select-Object SystemName,Caption,VolumeName,@{Name="Size(GB)"; Expression={"{0:N2}" -f ($_.Size/1GB)}},@{Name="Freespace(GB)"; Expression={"{0:N2}" -f ($_.Freespace/1GB)}}

  [font="Verdana"]Markus Bohse[/font]

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  November 15, 2011 at 3:16 am

  #1407697

  Perhaps this thread is of interest.

  ---

• jasonmorris

  SSCrazy

  Points: 2492

  More actions

  November 18, 2011 at 2:42 am

  #1409748

  I've always used sp_OAMethod and sp_OAGetProperty and there are plenty of examples via Google.

  You can get total space and free space this way.