Windows update may have broken sp_OAGetProperty and TotalSize

  • 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

  • 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

  • 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