XP_FIXEDDRIVES

Question

Post reply

XP_FIXEDDRIVES

David-155102

SSCarpal Tunnel

Points: 4361
More actions
April 2, 2008 at 5:20 am

#188105

I want to read the return values of XP_FIXEDDRIVES into two local variables e.g one for the drive and one for the space value. The reason for doing this is because I want to append the name of the server to the information so that I can query all of my servers and store the data in one table on one server.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

Something like this?

-- Temp table to store result

CREATE TABLE #DF_Result

(

Drive_Letter CHAR(1),

MBFree INT

)

--===== Fill that temp table

INSERT INTO #DF_Result

EXEC master..xp_fixeddrives

--=== Add the server column

ALTER TABLE #DF_Result

ADD Server VARCHAR(100) NULL ;

GO

--===== populate server column with servername

UPDATE #DF_Result

SET Server = CONVERT(VARCHAR(100), SERVERPROPERTY('Servername'))

--===== Get result

SELECT *

FROM #DF_Result

--== Clean up

DROP TABLE #DF_Result

SET NoCount OFF

-- Cory

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 2

Hi Cory,

Unfortunately I will have to query multiple servers so the update would set the server name field incorrectly.

Thanks

David

DBA-640728 SSChampion Points: 12898 More actions · Answer 3

hi David if you figure it out can you please post the answer, i have been trying to figure it out for days now. i got a similar code from this article:

http://www.sqlservercentral.com/columnists/hji/monitoringdiskspaceandsendingalertswithtsql.asp, and i am going nuts because i want to run it against my other servers.

Cory E. SSCertifiable Points: 7076 More actions · Answer 4

The code I posted above I lifted from this link (an article by David Bird that he got from Rodney Landrum):

http://www.sqlservercentral.com/articles/Integration+Services/61774/

It may take a bit, but you can clean this up to just get your disk drives, if you want. I get this to run across a list of SQL servers automatically.

Good luck!

-- Cory

SanjayAttray SSChampion Points: 13157 More actions · Answer 5

check if this helps.

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

drop table [dbo].[DiskAvailableSpace]

GO

CREATE TABLE [dbo].[DiskAvailableSpace] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[DriveLetter] [char] (1),

[FreeMB] [int] NOT NULL ,

[TimeCollected] [smalldatetime] NOT NULL

)

GO

ALTER TABLE [dbo].[DiskAvailableSpace] WITH NOCHECK ADD

CONSTRAINT [DF_DiskAvailableSpace_TimeCollected] DEFAULT (getdate()) FOR [TimeCollected]

GO

CREATE PROCEDURE usp_TrackDiskAvailableSpace AS

/*

Author:

Purpose: Trace and record xp_fixeddrives results into the DiskAvailableSpace table. The results will be invaluable

for trend analysis.

*/

SET NOCOUNT ON

/* Create a temp table to hold disk space information */

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))

DROP TABLE #disk_free_space

CREATE TABLE #disk_free_space (

DriveLetter CHAR(1) NOT NULL

,FreeMB INTEGER NOT NULL

)

/* Populate #disk_free_space with data */

INSERT INTO #disk_free_space

EXEC master..xp_fixeddrives

/* Populate DiskAvailableSpace with free space data. This table will have an identity field and time stamp */

INSERT INTO DiskAvailableSpace

(DriveLetter, FreeMB)

SELECT DriveLetter, FreeMB FROM #disk_free_space

/* Clean up. Drop the temp table */

DROP TABLE #disk_free_space

GO

SQL DBA.

DBA-640728 SSChampion Points: 12898 More actions · Answer 6

thanks but how do i use this script to run against my other servers?

DBA-640728 SSChampion Points: 12898 More actions · Answer 7

hi thanks for the link, i tried doing the article before but some of my servers are in the network but are not part of the domain. how do i get the information from those servers? :crying:

Cory E. SSCertifiable Points: 7076 More actions · Answer 8

DBA (4/2/2008)
hi thanks for the link, i tried doing the article before but some of my servers are in the network but are not part of the domain. how do i get the information from those servers? :crying:

Not part of the domain, or on a different subnet? I had to modify the linked package a bit to connect to machines on each of the subnets, and gather a list of servers using OSQL /Lc, and then modify the SSIS_ServerList to be a view of the other subnets union'ed together.

good luck!

-- Cory