November 21, 2011 at 4:55 am
Hi,
We use the script below (modified version of a standard script found via Google) to gather total and free disk space from our SQL boxes and since we have brought one up to date with windows patches the Total Size property returns the same value whichever drive we specify. All the non-patched servers are fine.
Has anybody seen this?
thanks,
Jason
declare @ServerName as varchar(60)
set @servername = 'yourservernamehere
CREATE TABLE TEMPDB.dbo.tFreeSpace
(
[ServerName] [varchar](50) NULL,
[Drive] [char](1) NULL,
[Total_size_MB] [int] NULL,
[Freespace_MB] [int] NULL,
[Percent_Free] [decimal](5, 2) NULL,
[Time_stamp] [datetime] NULL
)
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
IF @ServerName is null or @ServerName =''
BEGIN
SELECT @ServerName = '[' + @@servername + '].master.dbo.xp_fixeddrives'
END
ELSE SELECT @ServerName = '[' + @ServerName + '].master.dbo.xp_fixeddrives'
INSERT TEMPDB.dbo.tFreeSpace(drive,FreeSpace_MB)
EXEC @ServerName
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from TEMPDB.dbo.tFreeSpace
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
begin
print @odrive
print @drive
print @TotalSize
end
UPDATE TEMPDB.dbo.tFreeSpace
SET Total_size_MB=@TotalSize/@MB,
ServerName = replace( @ServerName , '.master.dbo.xp_fixeddrives',''),
Percent_Free = CAST((Freespace_MB/(@TotalSize/@MB*1.0))*100.0 as decimal(5,2)),
Time_stamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
November 21, 2011 at 5:09 am
Hi Jason
This stuff is much easier to read if you use code="sql" tags (+ thanks to SQL Prompt for reformatting):
declare @ServerName as varchar(60)
set @servername = 'yourservernamehere'
create table TEMPDB.dbo.tFreeSpace
(
[ServerName] [varchar](50) null,
[Drive] [char](1) null,
[Total_size_MB] [int] null,
[Freespace_MB] [int] null,
[Percent_Free] [decimal](5,2) null,
[Time_stamp] [datetime] null
)
set NOCOUNT on
declare @hr int
declare @fso int
declare @drive char(1)
declare @odrive int
declare @TotalSize varchar(20)
if @ServerName is null
or @ServerName = ''
begin
select @ServerName = '[' + @@servername + '].master.dbo.xp_fixeddrives'
end
else
select @ServerName = '[' + @ServerName + '].master.dbo.xp_fixeddrives'
insert TEMPDB.dbo.tFreeSpace
(drive,FreeSpace_MB)
exec @ServerName
exec @hr= sp_OACreate 'Scripting.FileSystemObject',@fso out
if @hr <> 0
exec sp_OAGetErrorInfo @fso
declare dcur cursor LOCAL FAST_FORWARD
for
select drive
from TEMPDB.dbo.tFreeSpace
order by drive
open dcur
fetch next from dcur into @drive
while @@FETCH_STATUS = 0
begin
exec @hr = sp_OAMethod @fso,'GetDrive',@odrive out,@drive
if @hr <> 0
exec sp_OAGetErrorInfo @fso
exec @hr = sp_OAGetProperty @odrive,'TotalSize',@TotalSize out
if @hr <> 0
exec sp_OAGetErrorInfo @odrive
begin
print @odrive
print @drive
print @TotalSize
end
update TEMPDB.dbo.tFreeSpace
set Total_size_MB = @TotalSize / @MB,
ServerName = replace(@ServerName,'.master.dbo.xp_fixeddrives',''),
Percent_Free = cast((Freespace_MB / (@TotalSize / @MB * 1.0))
* 100.0 as decimal(5,2)),
Time_stamp = (getdate())
where drive = @drive
fetch next from dcur into @drive
end
close dcur
deallocate dcur
exec @hr= sp_OADestroy @fso
if @hr <> 0
exec sp_OAGetErrorInfo @fso
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 21, 2011 at 5:11 am
Thanks Phil.
When I got the email saying I had a reply I thought somebody may have the answer 🙁
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply