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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy