November 15, 2011 at 2:35 am
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
November 15, 2011 at 2:48 am
As far as I know, you cant get it unless you use dos commands using xp_cmdshell.
Regards,
Raj
November 15, 2011 at 3:12 am
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]
November 18, 2011 at 2:42 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply