Post reply

T-SQL Problem ...

  • May 31, 2010 at 7:05 am

    #387046

    A brief description of what I'm trying to do,

    I have a process that writes the current drive space for each drive on a server (many servers) and reports this to a table. I record is logged every 15 minutes so I can watch drive space changes.

    I want to give users an idea of when this drive will run out of space based on previous usage. I would like them to provide the interval - based on the past XX days usage your drive will run out of space in 23 days. So based on the assumption that the growth is linear, I could tell that based on the last 1, 7, 30, 60, or 90 days usage I could give an educated answer.

    I know I need to get the average usage for each day but I think I need some assistance getting the difference for each day then getting the average difference. Any help would be greatly appreciated.

    Here is a script to create the table with some sample data. The sample data is for one Drive on one server.

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#myHostSpace','U') IS NOT NULL DROP TABLE #myHostSpace

    --===== Create the test table with

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE #myHostSpace(

    [uid] [bigint] IDENTITY(1,1) NOT NULL,

    [HostName] [varchar](50) NOT NULL,

    [DriveLetter] [varchar](50) NOT NULL,

    [TotalSpaceBytes] [bigint] NOT NULL,

    [FreeSpaceBytes] [bigint] NOT NULL,

    [UsedSpaceBytes] [bigint] NOT NULL,

    [HostDriveLabel] [varchar](50) NULL,

    [HostDriveFormat] [varchar](50) NOT NULL,

    [log_dt] [datetime] NOT NULL,

    [host_dt] [datetime] NULL,

    [interval] [nchar](10) NOT NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #myHostSpace ON

    INSERT INTO #myHostSpace

    (uid,HostName,DriveLetter,TotalSpaceBytes,FreeSpaceBytes,UsedSpaceBytes,HostDriveLabel,HostDriveFormat,log_dt,host_dt,interval)

    SELECT '897414','DC-SV-SQL1','E:\','231919841280','75962023936','155957817344','TransactionLogs','NTFS','May 1 2010 12:14AM','May 1 2010 12:14AM','900 ' UNION ALL

    SELECT '897466','DC-SV-SQL1','E:\','231919841280','75962023936','155957817344','TransactionLogs','NTFS','May 1 2010 12:29AM','May 1 2010 12:29AM','900 ' UNION ALL

    SELECT '897518','DC-SV-SQL1','E:\','231919841280','75962023936','155957817344','TransactionLogs','NTFS','May 1 2010 12:44AM','May 1 2010 12:44AM','900 ' UNION ALL

    SELECT '897570','DC-SV-SQL1','E:\','231919841280','75888230400','156031610880','TransactionLogs','NTFS','May 1 2010 12:59AM','May 1 2010 12:59AM','900 ' UNION ALL

    SELECT '897622','DC-SV-SQL1','E:\','231919841280','75888230400','156031610880','TransactionLogs','NTFS','May 1 2010 1:14AM','May 1 2010 1:14AM','900 ' UNION ALL

    SELECT '897674','DC-SV-SQL1','E:\','231919841280','75888230400','156031610880','TransactionLogs','NTFS','May 1 2010 1:29AM','May 1 2010 1:29AM','900 ' UNION ALL

    SELECT '897726','DC-SV-SQL1','E:\','231919841280','75888230400','156031610880','TransactionLogs','NTFS','May 1 2010 1:44AM','May 1 2010 1:44AM','900 ' UNION ALL

    SELECT '897778','DC-SV-SQL1','E:\','231919841280','75888492544','156031348736','TransactionLogs','NTFS','May 1 2010 1:59AM','May 1 2010 1:59AM','900 ' UNION ALL

    SELECT '897830','DC-SV-SQL1','E:\','231919841280','75888492544','156031348736','TransactionLogs','NTFS','May 1 2010 2:14AM','May 1 2010 2:14AM','900 ' UNION ALL

    SELECT '897882','DC-SV-SQL1','E:\','231919841280','75888492544','156031348736','TransactionLogs','NTFS','May 1 2010 2:29AM','May 1 2010 2:29AM','900 ' UNION ALL

    SELECT '897934','DC-SV-SQL1','E:\','231919841280','75491672064','156428169216','TransactionLogs','NTFS','May 1 2010 2:44AM','May 1 2010 2:44AM','900 ' UNION ALL

    SELECT '897986','DC-SV-SQL1','E:\','231919841280','75491672064','156428169216','TransactionLogs','NTFS','May 1 2010 2:59AM','May 1 2010 2:59AM','900 ' UNION ALL

    SELECT '898038','DC-SV-SQL1','E:\','231919841280','75491672064','156428169216','TransactionLogs','NTFS','May 1 2010 3:14AM','May 1 2010 3:14AM','900 ' UNION ALL

    SELECT '898090','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 3:29AM','May 1 2010 3:29AM','900 ' UNION ALL

    SELECT '898142','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 3:44AM','May 1 2010 3:44AM','900 ' UNION ALL

    SELECT '898194','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 3:59AM','May 1 2010 3:59AM','900 ' UNION ALL

    SELECT '898246','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 4:14AM','May 1 2010 4:14AM','900 ' UNION ALL

    SELECT '898298','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 4:29AM','May 1 2010 4:29AM','900 ' UNION ALL

    SELECT '898350','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 4:44AM','May 1 2010 4:44AM','900 ' UNION ALL

    SELECT '898402','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 4:59AM','May 1 2010 4:59AM','900 ' UNION ALL

    SELECT '898454','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 5:14AM','May 1 2010 5:14AM','900 ' UNION ALL

    SELECT '898506','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 5:29AM','May 1 2010 5:29AM','900 ' UNION ALL

    SELECT '898558','DC-SV-SQL1','E:\','231919841280','75421286400','156498554880','TransactionLogs','NTFS','May 1 2010 5:44AM','May 1 2010 5:44AM','900 ' UNION ALL

    SELECT '898610','DC-SV-SQL1','E:\','231919841280','75723931648','156195909632','TransactionLogs','NTFS','May 1 2010 5:59AM','May 1 2010 5:59AM','900 ' UNION ALL

    SELECT '898662','DC-SV-SQL1','E:\','231919841280','75723931648','156195909632','TransactionLogs','NTFS','May 1 2010 6:14AM','May 1 2010 6:14AM','900 ' UNION ALL

    SELECT '898714','DC-SV-SQL1','E:\','231919841280','75723931648','156195909632','TransactionLogs','NTFS','May 1 2010 6:29AM','May 1 2010 6:29AM','900 ' UNION ALL

    SELECT '898766','DC-SV-SQL1','E:\','231919841280','75723931648','156195909632','TransactionLogs','NTFS','May 1 2010 6:44AM','May 1 2010 6:44AM','900 ' UNION ALL

    SELECT '898818','DC-SV-SQL1','E:\','231919841280','75723931648','156195909632','TransactionLogs','NTFS','May 1 2010 6:59AM','May 1 2010 6:59AM','900 ' UNION ALL

    SELECT '898870','DC-SV-SQL1','E:\','231919841280','75723931648','156195909632','TransactionLogs','NTFS','May 1 2010 7:14AM','May 1 2010 7:14AM','900 ' UNION ALL

    SELECT '898922','DC-SV-SQL1','E:\','231919841280','75724455936','156195385344','TransactionLogs','NTFS','May 1 2010 7:29AM','May 1 2010 7:29AM','900 ' UNION ALL

    SELECT '898974','DC-SV-SQL1','E:\','231919841280','75724455936','156195385344','TransactionLogs','NTFS','May 1 2010 7:44AM','May 1 2010 7:44AM','900 ' UNION ALL

    SELECT '899026','DC-SV-SQL1','E:\','231919841280','75724455936','156195385344','TransactionLogs','NTFS','May 1 2010 7:59AM','May 1 2010 7:59AM','900 ' UNION ALL

    SELECT '899078','DC-SV-SQL1','E:\','231919841280','75724455936','156195385344','TransactionLogs','NTFS','May 1 2010 8:14AM','May 1 2010 8:14AM','900 ' UNION ALL

    SELECT '899130','DC-SV-SQL1','E:\','231919841280','75724455936','156195385344','TransactionLogs','NTFS','May 1 2010 8:29AM','May 1 2010 8:29AM','900 ' UNION ALL

    SELECT '899182','DC-SV-SQL1','E:\','231919841280','75724455936','156195385344','TransactionLogs','NTFS','May 1 2010 8:44AM','May 1 2010 8:44AM','900 ' UNION ALL

    SELECT '899234','DC-SV-SQL1','E:\','231919841280','75790778368','156129062912','TransactionLogs','NTFS','May 1 2010 8:59AM','May 1 2010 8:59AM','900 ' UNION ALL

    SELECT '899286','DC-SV-SQL1','E:\','231919841280','75790778368','156129062912','TransactionLogs','NTFS','May 1 2010 9:14AM','May 1 2010 9:14AM','900 ' UNION ALL

    SELECT '899338','DC-SV-SQL1','E:\','231919841280','75790778368','156129062912','TransactionLogs','NTFS','May 1 2010 9:29AM','May 1 2010 9:29AM','900 ' UNION ALL

    SELECT '899390','DC-SV-SQL1','E:\','231919841280','75790778368','156129062912','TransactionLogs','NTFS','May 1 2010 9:44AM','May 1 2010 9:44AM','900 ' UNION ALL

    SELECT '899442','DC-SV-SQL1','E:\','231919841280','75790778368','156129062912','TransactionLogs','NTFS','May 1 2010 9:59AM','May 1 2010 9:59AM','900 ' UNION ALL

    SELECT '899494','DC-SV-SQL1','E:\','231919841280','75790778368','156129062912','TransactionLogs','NTFS','May 1 2010 10:14AM','May 1 2010 10:14AM','900 ' UNION ALL

    SELECT '899546','DC-SV-SQL1','E:\','231919841280','75851030528','156068810752','TransactionLogs','NTFS','May 1 2010 10:29AM','May 1 2010 10:29AM','900 ' UNION ALL

    SELECT '899598','DC-SV-SQL1','E:\','231919841280','75848146944','156071694336','TransactionLogs','NTFS','May 1 2010 10:44AM','May 1 2010 10:44AM','900 ' UNION ALL

    SELECT '899650','DC-SV-SQL1','E:\','231919841280','76225437696','155694403584','TransactionLogs','NTFS','May 1 2010 10:59AM','May 1 2010 10:59AM','900 ' UNION ALL

    SELECT '899702','DC-SV-SQL1','E:\','231919841280','76225437696','155694403584','TransactionLogs','NTFS','May 1 2010 11:14AM','May 1 2010 11:14AM','900 ' UNION ALL

    SELECT '899754','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 11:29AM','May 1 2010 11:29AM','900 ' UNION ALL

    SELECT '899806','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 11:44AM','May 1 2010 11:44AM','900 ' UNION ALL

    SELECT '899858','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 11:59AM','May 1 2010 11:59AM','900 ' UNION ALL

    SELECT '899910','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 12:14PM','May 1 2010 12:14PM','900 ' UNION ALL

    SELECT '899962','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 12:29PM','May 1 2010 12:29PM','900 ' UNION ALL

    SELECT '900014','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 12:44PM','May 1 2010 12:44PM','900 ' UNION ALL

    SELECT '900066','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 12:59PM','May 1 2010 12:59PM','900 ' UNION ALL

    SELECT '900118','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 1:14PM','May 1 2010 1:14PM','900 ' UNION ALL

    SELECT '900170','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 1:29PM','May 1 2010 1:29PM','900 ' UNION ALL

    SELECT '900222','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 1:44PM','May 1 2010 1:44PM','900 ' UNION ALL

    SELECT '900274','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 1:59PM','May 1 2010 1:59PM','900 ' UNION ALL

    SELECT '900326','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 2:14PM','May 1 2010 2:14PM','900 ' UNION ALL

    SELECT '900378','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 2:29PM','May 1 2010 2:29PM','900 ' UNION ALL

    SELECT '900430','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 2:44PM','May 1 2010 2:44PM','900 ' UNION ALL

    SELECT '900482','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 2:59PM','May 1 2010 2:59PM','900 ' UNION ALL

    SELECT '900534','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 3:14PM','May 1 2010 3:14PM','900 ' UNION ALL

    SELECT '900586','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 3:29PM','May 1 2010 3:29PM','900 ' UNION ALL

    SELECT '900638','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 3:44PM','May 1 2010 3:44PM','900 ' UNION ALL

    SELECT '900690','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 3:59PM','May 1 2010 3:59PM','900 ' UNION ALL

    SELECT '900742','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 4:14PM','May 1 2010 4:14PM','900 ' UNION ALL

    SELECT '900794','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 4:29PM','May 1 2010 4:29PM','900 ' UNION ALL

    SELECT '900846','DC-SV-SQL1','E:\','231919841280','76267118592','155652722688','TransactionLogs','NTFS','May 1 2010 4:44PM','May 1 2010 4:44PM','900 ' UNION ALL

    SELECT '900898','DC-SV-SQL1','E:\','231919841280','76274065408','155645775872','TransactionLogs','NTFS','May 1 2010 4:59PM','May 1 2010 4:59PM','900 ' UNION ALL

    SELECT '900950','DC-SV-SQL1','E:\','231919841280','76274065408','155645775872','TransactionLogs','NTFS','May 1 2010 5:14PM','May 1 2010 5:14PM','900 ' UNION ALL

    SELECT '901002','DC-SV-SQL1','E:\','231919841280','76274065408','155645775872','TransactionLogs','NTFS','May 1 2010 5:29PM','May 1 2010 5:29PM','900 ' UNION ALL

    SELECT '901054','DC-SV-SQL1','E:\','231919841280','76274065408','155645775872','TransactionLogs','NTFS','May 1 2010 5:44PM','May 1 2010 5:44PM','900 ' UNION ALL

    SELECT '901106','DC-SV-SQL1','E:\','231919841280','76274065408','155645775872','TransactionLogs','NTFS','May 1 2010 5:59PM','May 1 2010 5:59PM','900 ' UNION ALL

    SELECT '901158','DC-SV-SQL1','E:\','231919841280','76274065408','155645775872','TransactionLogs','NTFS','May 1 2010 6:14PM','May 1 2010 6:14PM','900 ' UNION ALL

    SELECT '901210','DC-SV-SQL1','E:\','231919841280','76276948992','155642892288','TransactionLogs','NTFS','May 1 2010 6:29PM','May 1 2010 6:29PM','900 ' UNION ALL

    SELECT '901262','DC-SV-SQL1','E:\','231919841280','76276948992','155642892288','TransactionLogs','NTFS','May 1 2010 6:44PM','May 1 2010 6:44PM','900 ' UNION ALL

    SELECT '901314','DC-SV-SQL1','E:\','231919841280','76276948992','155642892288','TransactionLogs','NTFS','May 1 2010 6:59PM','May 1 2010 6:59PM','900 ' UNION ALL

    SELECT '901366','DC-SV-SQL1','E:\','231919841280','76276948992','155642892288','TransactionLogs','NTFS','May 1 2010 7:14PM','May 1 2010 7:14PM','900 ' UNION ALL

    SELECT '901418','DC-SV-SQL1','E:\','231919841280','76276948992','155642892288','TransactionLogs','NTFS','May 1 2010 7:29PM','May 1 2010 7:29PM','900 ' UNION ALL

    SELECT '901470','DC-SV-SQL1','E:\','231919841280','76276948992','155642892288','TransactionLogs','NTFS','May 1 2010 7:44PM','May 1 2010 7:44PM','900 ' UNION ALL

    SELECT '901522','DC-SV-SQL1','E:\','231919841280','76284878848','155634962432','TransactionLogs','NTFS','May 1 2010 7:59PM','May 1 2010 7:59PM','900 ' UNION ALL

    SELECT '901574','DC-SV-SQL1','E:\','231919841280','76284878848','155634962432','TransactionLogs','NTFS','May 1 2010 8:14PM','May 1 2010 8:14PM','900 ' UNION ALL

    SELECT '901626','DC-SV-SQL1','E:\','231919841280','76284878848','155634962432','TransactionLogs','NTFS','May 1 2010 8:29PM','May 1 2010 8:29PM','900 ' UNION ALL

    SELECT '901678','DC-SV-SQL1','E:\','231919841280','76284878848','155634962432','TransactionLogs','NTFS','May 1 2010 8:44PM','May 1 2010 8:44PM','900 ' UNION ALL

    SELECT '901730','DC-SV-SQL1','E:\','231919841280','76284878848','155634962432','TransactionLogs','NTFS','May 1 2010 8:59PM','May 1 2010 8:59PM','900 ' UNION ALL

    SELECT '901782','DC-SV-SQL1','E:\','231919841280','76284878848','155634962432','TransactionLogs','NTFS','May 1 2010 9:14PM','May 1 2010 9:14PM','900 ' UNION ALL

    SELECT '901834','DC-SV-SQL1','E:\','231919841280','76173664256','155746177024','TransactionLogs','NTFS','May 1 2010 9:29PM','May 1 2010 9:29PM','900 ' UNION ALL

    SELECT '901886','DC-SV-SQL1','E:\','231919841280','76180914176','155738927104','TransactionLogs','NTFS','May 1 2010 9:44PM','May 1 2010 9:44PM','900 ' UNION ALL

    SELECT '901938','DC-SV-SQL1','E:\','231919841280','76180914176','155738927104','TransactionLogs','NTFS','May 1 2010 9:59PM','May 1 2010 9:59PM','900 ' UNION ALL

    SELECT '901990','DC-SV-SQL1','E:\','231919841280','76195405824','155724435456','TransactionLogs','NTFS','May 1 2010 10:14PM','May 1 2010 10:14PM','900 ' UNION ALL

    SELECT '902042','DC-SV-SQL1','E:\','231919841280','76195405824','155724435456','TransactionLogs','NTFS','May 1 2010 10:29PM','May 1 2010 10:29PM','900 ' UNION ALL

    SELECT '902094','DC-SV-SQL1','E:\','231919841280','76200517632','155719323648','TransactionLogs','NTFS','May 1 2010 10:44PM','May 1 2010 10:44PM','900 ' UNION ALL

    SELECT '902146','DC-SV-SQL1','E:\','231919841280','76200517632','155719323648','TransactionLogs','NTFS','May 1 2010 10:59PM','May 1 2010 10:59PM','900 ' UNION ALL

    SELECT '902198','DC-SV-SQL1','E:\','231919841280','76200517632','155719323648','TransactionLogs','NTFS','May 1 2010 11:14PM','May 1 2010 11:14PM','900 ' UNION ALL

    SELECT '902250','DC-SV-SQL1','E:\','231919841280','76200517632','155719323648','TransactionLogs','NTFS','May 1 2010 11:29PM','May 1 2010 11:29PM','900 ' UNION ALL

    SELECT '902302','DC-SV-SQL1','E:\','231919841280','75902984192','156016857088','TransactionLogs','NTFS','May 1 2010 11:44PM','May 1 2010 11:44PM','900 ' UNION ALL

    SELECT '902354','DC-SV-SQL1','E:\','231919841280','75330002944','156589838336','TransactionLogs','NTFS','May 1 2010 11:59PM','May 1 2010 11:59PM','900 ' UNION ALL

    SELECT '902406','DC-SV-SQL1','E:\','231919841280','74973618176','156946223104','TransactionLogs','NTFS','May 2 2010 12:14AM','May 2 2010 12:14AM','900 ' UNION ALL

    SELECT '902458','DC-SV-SQL1','E:\','231919841280','74973618176','156946223104','TransactionLogs','NTFS','May 2 2010 12:29AM','May 2 2010 12:29AM','900 ' UNION ALL

    SELECT '902510','DC-SV-SQL1','E:\','231919841280','74973618176','156946223104','TransactionLogs','NTFS','May 2 2010 12:44AM','May 2 2010 12:44AM','900 ' UNION ALL

    SELECT '902562','DC-SV-SQL1','E:\','231919841280','74899365888','157020475392','TransactionLogs','NTFS','May 2 2010 12:59AM','May 2 2010 12:59AM','900 ' UNION ALL

    SELECT '902614','DC-SV-SQL1','E:\','231919841280','74899365888','157020475392','TransactionLogs','NTFS','May 2 2010 1:14AM','May 2 2010 1:14AM','900 ' UNION ALL

    SELECT '902666','DC-SV-SQL1','E:\','231919841280','74899365888','157020475392','TransactionLogs','NTFS','May 2 2010 1:29AM','May 2 2010 1:29AM','900 ' UNION ALL

    SELECT '902718','DC-SV-SQL1','E:\','231919841280','74899365888','157020475392','TransactionLogs','NTFS','May 2 2010 1:44AM','May 2 2010 1:44AM','900 ' UNION ALL

    SELECT '902770','DC-SV-SQL1','E:\','231919841280','74899365888','157020475392','TransactionLogs','NTFS','May 2 2010 1:59AM','May 2 2010 1:59AM','900 ' UNION ALL

    SELECT '902822','DC-SV-SQL1','E:\','231919841280','74899365888','157020475392','TransactionLogs','NTFS','May 2 2010 2:14AM','May 2 2010 2:14AM','900 ' UNION ALL

    SELECT '902874','DC-SV-SQL1','E:\','231919841280','74899365888','157020475392','TransactionLogs','NTFS','May 2 2010 2:29AM','May 2 2010 2:29AM','900 ' UNION ALL

    SELECT '902926','DC-SV-SQL1','E:\','231919841280','74520494080','157399347200','TransactionLogs','NTFS','May 2 2010 2:44AM','May 2 2010 2:44AM','900 ' UNION ALL

    SELECT '902978','DC-SV-SQL1','E:\','231919841280','74520494080','157399347200','TransactionLogs','NTFS','May 2 2010 2:59AM','May 2 2010 2:59AM','900 ' UNION ALL

    SELECT '903030','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 3:14AM','May 2 2010 3:14AM','900 ' UNION ALL

    SELECT '903082','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 3:29AM','May 2 2010 3:29AM','900 ' UNION ALL

    SELECT '903134','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 3:44AM','May 2 2010 3:44AM','900 ' UNION ALL

    SELECT '903186','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 3:59AM','May 2 2010 3:59AM','900 ' UNION ALL

    SELECT '903238','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 4:14AM','May 2 2010 4:14AM','900 ' UNION ALL

    SELECT '903290','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 4:29AM','May 2 2010 4:29AM','900 ' UNION ALL

    SELECT '903342','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 4:44AM','May 2 2010 4:44AM','900 ' UNION ALL

    SELECT '903394','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 4:59AM','May 2 2010 4:59AM','900 ' UNION ALL

    SELECT '903446','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 5:14AM','May 2 2010 5:14AM','900 ' UNION ALL

    SELECT '903498','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 5:29AM','May 2 2010 5:29AM','900 ' UNION ALL

    SELECT '903550','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 5:44AM','May 2 2010 5:44AM','900 ' UNION ALL

    SELECT '903602','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 5:59AM','May 2 2010 5:59AM','900 ' UNION ALL

    SELECT '903654','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 6:14AM','May 2 2010 6:14AM','900 ' UNION ALL

    SELECT '903706','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 6:29AM','May 2 2010 6:29AM','900 ' UNION ALL

    SELECT '903758','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 6:44AM','May 2 2010 6:44AM','900 ' UNION ALL

    SELECT '903810','DC-SV-SQL1','E:\','231919841280','74452992000','157466849280','TransactionLogs','NTFS','May 2 2010 6:59AM','May 2 2010 6:59AM','900 ' UNION ALL

    SELECT '903862','DC-SV-SQL1','E:\','231919841280','67441033216','164478808064','TransactionLogs','NTFS','May 2 2010 7:14AM','May 2 2010 7:14AM','900 ' UNION ALL

    SELECT '903914','DC-SV-SQL1','E:\','231919841280','57741705216','174178136064','TransactionLogs','NTFS','May 2 2010 7:29AM','May 2 2010 7:29AM','900 ' UNION ALL

    SELECT '903966','DC-SV-SQL1','E:\','231919841280','54071689216','177848152064','TransactionLogs','NTFS','May 2 2010 7:44AM','May 2 2010 7:44AM','900 ' UNION ALL

    SELECT '904018','DC-SV-SQL1','E:\','231919841280','54071689216','177848152064','TransactionLogs','NTFS','May 2 2010 7:59AM','May 2 2010 7:59AM','900 ' UNION ALL

    SELECT '904070','DC-SV-SQL1','E:\','231919841280','54071689216','177848152064','TransactionLogs','NTFS','May 2 2010 8:14AM','May 2 2010 8:14AM','900 ' UNION ALL

    SELECT '904122','DC-SV-SQL1','E:\','231919841280','54071689216','177848152064','TransactionLogs','NTFS','May 2 2010 8:29AM','May 2 2010 8:29AM','900 ' UNION ALL

    SELECT '904174','DC-SV-SQL1','E:\','231919841280','54071689216','177848152064','TransactionLogs','NTFS','May 2 2010 8:44AM','May 2 2010 8:44AM','900 ' UNION ALL

    SELECT '904226','DC-SV-SQL1','E:\','231919841280','41931603968','189988237312','TransactionLogs','NTFS','May 2 2010 8:59AM','May 2 2010 8:59AM','900 ' UNION ALL

    SELECT '904278','DC-SV-SQL1','E:\','231919841280','41990520832','189929320448','TransactionLogs','NTFS','May 2 2010 9:14AM','May 2 2010 9:14AM','900 ' UNION ALL

    SELECT '904330','DC-SV-SQL1','E:\','231919841280','41990520832','189929320448','TransactionLogs','NTFS','May 2 2010 9:29AM','May 2 2010 9:29AM','900 ' UNION ALL

    SELECT '904382','DC-SV-SQL1','E:\','231919841280','42073751552','189846089728','TransactionLogs','NTFS','May 2 2010 9:44AM','May 2 2010 9:44AM','900 ' UNION ALL

    SELECT '904434','DC-SV-SQL1','E:\','231919841280','42073751552','189846089728','TransactionLogs','NTFS','May 2 2010 9:59AM','May 2 2010 9:59AM','900 ' UNION ALL

    SELECT '904486','DC-SV-SQL1','E:\','231919841280','42073751552','189846089728','TransactionLogs','NTFS','May 2 2010 10:14AM','May 2 2010 10:14AM','900 ' UNION ALL

    SELECT '904538','DC-SV-SQL1','E:\','231919841280','42073751552','189846089728','TransactionLogs','NTFS','May 2 2010 10:29AM','May 2 2010 10:29AM','900 ' UNION ALL

    SELECT '904590','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 10:44AM','May 2 2010 10:44AM','900 ' UNION ALL

    SELECT '904642','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 10:59AM','May 2 2010 10:59AM','900 ' UNION ALL

    SELECT '904694','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 11:14AM','May 2 2010 11:14AM','900 ' UNION ALL

    SELECT '904746','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 11:29AM','May 2 2010 11:29AM','900 ' UNION ALL

    SELECT '904798','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 11:44AM','May 2 2010 11:44AM','900 ' UNION ALL

    SELECT '904850','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 11:59AM','May 2 2010 11:59AM','900 ' UNION ALL

    SELECT '904902','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 12:14PM','May 2 2010 12:14PM','900 ' UNION ALL

    SELECT '904954','DC-SV-SQL1','E:\','231919841280','42070867968','189848973312','TransactionLogs','NTFS','May 2 2010 12:29PM','May 2 2010 12:29PM','900 ' UNION ALL

    SELECT '905006','DC-SV-SQL1','E:\','231919841280','42072047616','189847793664','TransactionLogs','NTFS','May 2 2010 12:44PM','May 2 2010 12:44PM','900 ' UNION ALL

    SELECT '905058','DC-SV-SQL1','E:\','231919841280','42072047616','189847793664','TransactionLogs','NTFS','May 2 2010 12:59PM','May 2 2010 12:59PM','900 ' UNION ALL

    SELECT '905110','DC-SV-SQL1','E:\','231919841280','42072047616','189847793664','TransactionLogs','NTFS','May 2 2010 1:14PM','May 2 2010 1:14PM','900 ' UNION ALL

    SELECT '905162','DC-SV-SQL1','E:\','231919841280','42072047616','189847793664','TransactionLogs','NTFS','May 2 2010 1:29PM','May 2 2010 1:29PM','900 ' UNION ALL

    SELECT '905214','DC-SV-SQL1','E:\','231919841280','42072047616','189847793664','TransactionLogs','NTFS','May 2 2010 1:44PM','May 2 2010 1:44PM','900 ' UNION ALL

    SELECT '905266','DC-SV-SQL1','E:\','231919841280','42072047616','189847793664','TransactionLogs','NTFS','May 2 2010 1:59PM','May 2 2010 1:59PM','900 ' UNION ALL

    SELECT '905318','DC-SV-SQL1','E:\','231919841280','42142433280','189777408000','TransactionLogs','NTFS','May 2 2010 2:14PM','May 2 2010 2:14PM','900 ' UNION ALL

    SELECT '905370','DC-SV-SQL1','E:\','231919841280','42142433280','189777408000','TransactionLogs','NTFS','May 2 2010 2:29PM','May 2 2010 2:29PM','900 ' UNION ALL

    SELECT '905422','DC-SV-SQL1','E:\','231919841280','42446127104','189473714176','TransactionLogs','NTFS','May 2 2010 2:44PM','May 2 2010 2:44PM','900 ' UNION ALL

    SELECT '905474','DC-SV-SQL1','E:\','231919841280','42446127104','189473714176','TransactionLogs','NTFS','May 2 2010 2:59PM','May 2 2010 2:59PM','900 ' UNION ALL

    SELECT '905526','DC-SV-SQL1','E:\','231919841280','42699030528','189220810752','TransactionLogs','NTFS','May 2 2010 3:14PM','May 2 2010 3:14PM','900 ' UNION ALL

    SELECT '905578','DC-SV-SQL1','E:\','231919841280','42699030528','189220810752','TransactionLogs','NTFS','May 2 2010 3:29PM','May 2 2010 3:29PM','900 ' UNION ALL

    SELECT '905630','DC-SV-SQL1','E:\','231919841280','42699030528','189220810752','TransactionLogs','NTFS','May 2 2010 3:44PM','May 2 2010 3:44PM','900 ' UNION ALL

    SELECT '905682','DC-SV-SQL1','E:\','231919841280','42699030528','189220810752','TransactionLogs','NTFS','May 2 2010 3:59PM','May 2 2010 3:59PM','900 ' UNION ALL

    SELECT '905734','DC-SV-SQL1','E:\','231919841280','42699030528','189220810752','TransactionLogs','NTFS','May 2 2010 4:14PM','May 2 2010 4:14PM','900 ' UNION ALL

    SELECT '905786','DC-SV-SQL1','E:\','231919841280','42699030528','189220810752','TransactionLogs','NTFS','May 2 2010 4:29PM','May 2 2010 4:29PM','900 ' UNION ALL

    SELECT '905838','DC-SV-SQL1','E:\','231919841280','42701127680','189218713600','TransactionLogs','NTFS','May 2 2010 4:44PM','May 2 2010 4:44PM','900 ' UNION ALL

    SELECT '905890','DC-SV-SQL1','E:\','231919841280','42701127680','189218713600','TransactionLogs','NTFS','May 2 2010 4:59PM','May 2 2010 4:59PM','900 ' UNION ALL

    SELECT '905942','DC-SV-SQL1','E:\','231919841280','42701127680','189218713600','TransactionLogs','NTFS','May 2 2010 5:14PM','May 2 2010 5:14PM','900 ' UNION ALL

    SELECT '905994','DC-SV-SQL1','E:\','231919841280','42701127680','189218713600','TransactionLogs','NTFS','May 2 2010 5:29PM','May 2 2010 5:29PM','900 ' UNION ALL

    SELECT '906046','DC-SV-SQL1','E:\','231919841280','42701127680','189218713600','TransactionLogs','NTFS','May 2 2010 5:44PM','May 2 2010 5:44PM','900 ' UNION ALL

    SELECT '906098','DC-SV-SQL1','E:\','231919841280','42701127680','189218713600','TransactionLogs','NTFS','May 2 2010 5:59PM','May 2 2010 5:59PM','900 ' UNION ALL

    SELECT '906150','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 6:14PM','May 2 2010 6:14PM','900 ' UNION ALL

    SELECT '906202','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 6:29PM','May 2 2010 6:29PM','900 ' UNION ALL

    SELECT '906254','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 6:44PM','May 2 2010 6:44PM','900 ' UNION ALL

    SELECT '906306','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 6:59PM','May 2 2010 6:59PM','900 ' UNION ALL

    SELECT '906358','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 7:14PM','May 2 2010 7:14PM','900 ' UNION ALL

    SELECT '906410','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 7:29PM','May 2 2010 7:29PM','900 ' UNION ALL

    SELECT '906462','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 7:44PM','May 2 2010 7:44PM','900 ' UNION ALL

    SELECT '906514','DC-SV-SQL1','E:\','231919841280','42708271104','189211570176','TransactionLogs','NTFS','May 2 2010 7:59PM','May 2 2010 7:59PM','900 ' UNION ALL

    SELECT '906566','DC-SV-SQL1','E:\','231919841280','42710368256','189209473024','TransactionLogs','NTFS','May 2 2010 8:14PM','May 2 2010 8:14PM','900 ' UNION ALL

    SELECT '906618','DC-SV-SQL1','E:\','231919841280','42710368256','189209473024','TransactionLogs','NTFS','May 2 2010 8:29PM','May 2 2010 8:29PM','900 ' UNION ALL

    SELECT '906670','DC-SV-SQL1','E:\','231919841280','42719739904','189200101376','TransactionLogs','NTFS','May 2 2010 8:44PM','May 2 2010 8:44PM','900 ' UNION ALL

    SELECT '906722','DC-SV-SQL1','E:\','231919841280','42719739904','189200101376','TransactionLogs','NTFS','May 2 2010 8:59PM','May 2 2010 8:59PM','900 ' UNION ALL

    SELECT '906774','DC-SV-SQL1','E:\','231919841280','42719739904','189200101376','TransactionLogs','NTFS','May 2 2010 9:14PM','May 2 2010 9:14PM','900 ' UNION ALL

    SELECT '906826','DC-SV-SQL1','E:\','231919841280','42719739904','189200101376','TransactionLogs','NTFS','May 2 2010 9:29PM','May 2 2010 9:29PM','900 ' UNION ALL

    SELECT '906878','DC-SV-SQL1','E:\','231919841280','42719739904','189200101376','TransactionLogs','NTFS','May 2 2010 9:44PM','May 2 2010 9:44PM','900 ' UNION ALL

    SELECT '906930','DC-SV-SQL1','E:\','231919841280','42719739904','189200101376','TransactionLogs','NTFS','May 2 2010 9:59PM','May 2 2010 9:59PM','900 ' UNION ALL

    SELECT '906982','DC-SV-SQL1','E:\','231919841280','42720002048','189199839232','TransactionLogs','NTFS','May 2 2010 10:14PM','May 2 2010 10:14PM','900 ' UNION ALL

    SELECT '907034','DC-SV-SQL1','E:\','231919841280','42720002048','189199839232','TransactionLogs','NTFS','May 2 2010 10:29PM','May 2 2010 10:29PM','900 ' UNION ALL

    SELECT '907086','DC-SV-SQL1','E:\','231919841280','42720002048','189199839232','TransactionLogs','NTFS','May 2 2010 10:44PM','May 2 2010 10:44PM','900 ' UNION ALL

    SELECT '907138','DC-SV-SQL1','E:\','231919841280','42720002048','189199839232','TransactionLogs','NTFS','May 2 2010 10:59PM','May 2 2010 10:59PM','900 ' UNION ALL

    SELECT '907190','DC-SV-SQL1','E:\','231919841280','42720002048','189199839232','TransactionLogs','NTFS','May 2 2010 11:14PM','May 2 2010 11:14PM','900 ' UNION ALL

    SELECT '907242','DC-SV-SQL1','E:\','231919841280','42720002048','189199839232','TransactionLogs','NTFS','May 2 2010 11:29PM','May 2 2010 11:29PM','900 ' UNION ALL

    SELECT '907294','DC-SV-SQL1','E:\','231919841280','42720395264','189199446016','TransactionLogs','NTFS','May 2 2010 11:44PM','May 2 2010 11:44PM','900 ' UNION ALL

    SELECT '907346','DC-SV-SQL1','E:\','231919841280','42720395264','189199446016','TransactionLogs','NTFS','May 2 2010 11:59PM','May 2 2010 11:59PM','900 ' UNION ALL

    SELECT '907398','DC-SV-SQL1','E:\','231919841280','42390487040','189529354240','TransactionLogs','NTFS','May 3 2010 12:14AM','May 3 2010 12:14AM','900 ' UNION ALL

    SELECT '907450','DC-SV-SQL1','E:\','231919841280','42390487040','189529354240','TransactionLogs','NTFS','May 3 2010 12:29AM','May 3 2010 12:29AM','900 ' UNION ALL

    SELECT '907502','DC-SV-SQL1','E:\','231919841280','42390487040','189529354240','TransactionLogs','NTFS','May 3 2010 12:44AM','May 3 2010 12:44AM','900 ' UNION ALL

    SELECT '907554','DC-SV-SQL1','E:\','231919841280','42315841536','189603999744','TransactionLogs','NTFS','May 3 2010 12:59AM','May 3 2010 12:59AM','900 ' UNION ALL

    SELECT '907606','DC-SV-SQL1','E:\','231919841280','42315841536','189603999744','TransactionLogs','NTFS','May 3 2010 1:14AM','May 3 2010 1:14AM','900 ' UNION ALL

    SELECT '907658','DC-SV-SQL1','E:\','231919841280','42315841536','189603999744','TransactionLogs','NTFS','May 3 2010 1:29AM','May 3 2010 1:29AM','900 ' UNION ALL

    SELECT '907710','DC-SV-SQL1','E:\','231919841280','42329751552','189590089728','TransactionLogs','NTFS','May 3 2010 1:44AM','May 3 2010 1:44AM','900 ' UNION ALL

    SELECT '907762','DC-SV-SQL1','E:\','231919841280','42329751552','189590089728','TransactionLogs','NTFS','May 3 2010 1:59AM','May 3 2010 1:59AM','900 ' UNION ALL

    SELECT '907814','DC-SV-SQL1','E:\','231919841280','42329751552','189590089728','TransactionLogs','NTFS','May 3 2010 2:14AM','May 3 2010 2:14AM','900 ' UNION ALL

    SELECT '907866','DC-SV-SQL1','E:\','231919841280','42329751552','189590089728','TransactionLogs','NTFS','May 3 2010 2:29AM','May 3 2010 2:29AM','900 ' UNION ALL

    SELECT '907918','DC-SV-SQL1','E:\','231919841280','41974661120','189945180160','TransactionLogs','NTFS','May 3 2010 2:44AM','May 3 2010 2:44AM','900 ' UNION ALL

    SELECT '907970','DC-SV-SQL1','E:\','231919841280','41974661120','189945180160','TransactionLogs','NTFS','May 3 2010 2:59AM','May 3 2010 2:59AM','900 ' UNION ALL

    SELECT '908022','DC-SV-SQL1','E:\','231919841280','41974661120','189945180160','TransactionLogs','NTFS','May 3 2010 3:14AM','May 3 2010 3:14AM','900 ' UNION ALL

    SELECT '908074','DC-SV-SQL1','E:\','231919841280','41904275456','190015565824','TransactionLogs','NTFS','May 3 2010 3:29AM','May 3 2010 3:29AM','900 ' UNION ALL

    SELECT '908126','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 3:44AM','May 3 2010 3:44AM','900 ' UNION ALL

    SELECT '908178','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 3:59AM','May 3 2010 3:59AM','900 ' UNION ALL

    SELECT '908230','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 4:14AM','May 3 2010 4:14AM','900 ' UNION ALL

    SELECT '908282','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 4:29AM','May 3 2010 4:29AM','900 ' UNION ALL

    SELECT '908334','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 4:44AM','May 3 2010 4:44AM','900 ' UNION ALL

    SELECT '908386','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 4:59AM','May 3 2010 4:59AM','900 ' UNION ALL

    SELECT '908438','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 5:14AM','May 3 2010 5:14AM','900 ' UNION ALL

    SELECT '908490','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 5:29AM','May 3 2010 5:29AM','900 ' UNION ALL

    SELECT '908542','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 5:44AM','May 3 2010 5:44AM','900 ' UNION ALL

    SELECT '908594','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 5:59AM','May 3 2010 5:59AM','900 ' UNION ALL

    SELECT '908646','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 6:14AM','May 3 2010 6:14AM','900 ' UNION ALL

    SELECT '908698','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 6:29AM','May 3 2010 6:29AM','900 ' UNION ALL

    SELECT '908750','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 6:44AM','May 3 2010 6:44AM','900 ' UNION ALL

    SELECT '908802','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 6:59AM','May 3 2010 6:59AM','900 ' UNION ALL

    SELECT '908854','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 7:14AM','May 3 2010 7:14AM','900 ' UNION ALL

    SELECT '908906','DC-SV-SQL1','E:\','231919841280','41904799744','190015041536','TransactionLogs','NTFS','May 3 2010 7:29AM','May 3 2010 7:29AM','900 ' UNION ALL

    SELECT '908958','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 7:44AM','May 3 2010 7:44AM','900 ' UNION ALL

    SELECT '909010','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 7:59AM','May 3 2010 7:59AM','900 ' UNION ALL

    SELECT '909062','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 8:14AM','May 3 2010 8:14AM','900 ' UNION ALL

    SELECT '909114','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 8:29AM','May 3 2010 8:29AM','900 ' UNION ALL

    SELECT '909166','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 8:44AM','May 3 2010 8:44AM','900 ' UNION ALL

    SELECT '909218','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 8:59AM','May 3 2010 8:59AM','900 ' UNION ALL

    SELECT '909270','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 9:14AM','May 3 2010 9:14AM','900 ' UNION ALL

    SELECT '909322','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 9:29AM','May 3 2010 9:29AM','900 ' UNION ALL

    SELECT '909374','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 9:44AM','May 3 2010 9:44AM','900 ' UNION ALL

    SELECT '909426','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 9:59AM','May 3 2010 9:59AM','900 ' UNION ALL

    SELECT '909478','DC-SV-SQL1','E:\','231919841280','42231234560','189688606720','TransactionLogs','NTFS','May 3 2010 10:14AM','May 3 2010 10:14AM','900 ' UNION ALL

    SELECT '909530','DC-SV-SQL1','E:\','231919841280','42228350976','189691490304','TransactionLogs','NTFS','May 3 2010 10:29AM','May 3 2010 10:29AM','900 ' UNION ALL

    SELECT '909582','DC-SV-SQL1','E:\','231919841280','42294280192','189625561088','TransactionLogs','NTFS','May 3 2010 10:45AM','May 3 2010 10:44AM','900 ' UNION ALL

    SELECT '909634','DC-SV-SQL1','E:\','231919841280','42294280192','189625561088','TransactionLogs','NTFS','May 3 2010 10:59AM','May 3 2010 10:59AM','900 ' UNION ALL

    SELECT '909686','DC-SV-SQL1','E:\','231919841280','42294280192','189625561088','TransactionLogs','NTFS','May 3 2010 11:14AM','May 3 2010 11:14AM','900 ' UNION ALL

    SELECT '909738','DC-SV-SQL1','E:\','231919841280','42294280192','189625561088','TransactionLogs','NTFS','May 3 2010 11:29AM','May 3 2010 11:29AM','900 ' UNION ALL

    SELECT '909790','DC-SV-SQL1','E:\','231919841280','42294280192','189625561088','TransactionLogs','NTFS','May 3 2010 11:45AM','May 3 2010 11:44AM','900 ' UNION ALL

    SELECT '909869','DC-SV-SQL1','E:\','231919841280','42353983488','189565857792','TransactionLogs','NTFS','May 3 2010 12:14PM','May 3 2010 12:14PM','900 ' UNION ALL

    SELECT '909921','DC-SV-SQL1','E:\','231919841280','42353983488','189565857792','TransactionLogs','NTFS','May 3 2010 12:29PM','May 3 2010 12:29PM','900 ' UNION ALL

    SELECT '909973','DC-SV-SQL1','E:\','231919841280','42693328896','189226512384','TransactionLogs','NTFS','May 3 2010 12:44PM','May 3 2010 12:44PM','900 ' UNION ALL

    SELECT '910025','DC-SV-SQL1','E:\','231919841280','42693328896','189226512384','TransactionLogs','NTFS','May 3 2010 12:59PM','May 3 2010 12:59PM','900 ' UNION ALL

    SELECT '910077','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 1:14PM','May 3 2010 1:14PM','900 ' UNION ALL

    SELECT '910129','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 1:29PM','May 3 2010 1:29PM','900 ' UNION ALL

    SELECT '910182','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 1:44PM','May 3 2010 1:44PM','900 ' UNION ALL

    SELECT '910235','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 1:59PM','May 3 2010 1:59PM','900 ' UNION ALL

    SELECT '910287','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 2:14PM','May 3 2010 2:14PM','900 ' UNION ALL

    SELECT '910340','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 2:29PM','May 3 2010 2:29PM','900 ' UNION ALL

    SELECT '910393','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 2:44PM','May 3 2010 2:44PM','900 ' UNION ALL

    SELECT '910446','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 2:59PM','May 3 2010 2:59PM','900 ' UNION ALL

    SELECT '910499','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 3:14PM','May 3 2010 3:14PM','900 ' UNION ALL

    SELECT '910552','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 3:29PM','May 3 2010 3:29PM','900 ' UNION ALL

    SELECT '910605','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 3:44PM','May 3 2010 3:44PM','900 ' UNION ALL

    SELECT '910658','DC-SV-SQL1','E:\','231919841280','42694246400','189225594880','TransactionLogs','NTFS','May 3 2010 3:59PM','May 3 2010 3:59PM','900 ' UNION ALL

    SELECT '910711','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 4:14PM','May 3 2010 4:14PM','900 ' UNION ALL

    SELECT '910764','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 4:29PM','May 3 2010 4:29PM','900 ' UNION ALL

    SELECT '910817','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 4:44PM','May 3 2010 4:44PM','900 ' UNION ALL

    SELECT '910870','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 4:59PM','May 3 2010 4:59PM','900 ' UNION ALL

    SELECT '910923','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 5:14PM','May 3 2010 5:14PM','900 ' UNION ALL

    SELECT '910976','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 5:29PM','May 3 2010 5:29PM','900 ' UNION ALL

    SELECT '911029','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 5:44PM','May 3 2010 5:44PM','900 ' UNION ALL

    SELECT '911082','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 5:59PM','May 3 2010 5:59PM','900 ' UNION ALL

    SELECT '911135','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 6:14PM','May 3 2010 6:14PM','900 ' UNION ALL

    SELECT '911188','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 6:29PM','May 3 2010 6:29PM','900 ' UNION ALL

    SELECT '911241','DC-SV-SQL1','E:\','231919841280','42695426048','189224415232','TransactionLogs','NTFS','May 3 2010 6:44PM','May 3 2010 6:44PM','900 ' UNION ALL

    SELECT '911294','DC-SV-SQL1','E:\','231919841280','42708082688','189211758592','TransactionLogs','NTFS','May 3 2010 6:59PM','May 3 2010 6:59PM','900 ' UNION ALL

    SELECT '911347','DC-SV-SQL1','E:\','231919841280','42708082688','189211758592','TransactionLogs','NTFS','May 3 2010 7:14PM','May 3 2010 7:14PM','900 ' UNION ALL

    SELECT '911400','DC-SV-SQL1','E:\','231919841280','42708082688','189211758592','TransactionLogs','NTFS','May 3 2010 7:29PM','May 3 2010 7:29PM','900 ' UNION ALL

    SELECT '911453','DC-SV-SQL1','E:\','231919841280','42708082688','189211758592','TransactionLogs','NTFS','May 3 2010 7:44PM','May 3 2010 7:44PM','900 ' UNION ALL

    SELECT '911506','DC-SV-SQL1','E:\','231919841280','42708082688','189211758592','TransactionLogs','NTFS','May 3 2010 7:59PM','May 3 2010 7:59PM','900 ' UNION ALL

    SELECT '911559','DC-SV-SQL1','E:\','231919841280','42708082688','189211758592','TransactionLogs','NTFS','May 3 2010 8:14PM','May 3 2010 8:14PM','900 ' UNION ALL

    SELECT '911612','DC-SV-SQL1','E:\','231919841280','42710179840','189209661440','TransactionLogs','NTFS','May 3 2010 8:29PM','May 3 2010 8:29PM','900 ' UNION ALL

    SELECT '911664','DC-SV-SQL1','E:\','231919841280','42710179840','189209661440','TransactionLogs','NTFS','May 3 2010 8:44PM','May 3 2010 8:44PM','900 ' UNION ALL

    SELECT '911717','DC-SV-SQL1','E:\','231919841280','42710179840','189209661440','TransactionLogs','NTFS','May 3 2010 8:59PM','May 3 2010 8:59PM','900 ' UNION ALL

    SELECT '911770','DC-SV-SQL1','E:\','231919841280','42710179840','189209661440','TransactionLogs','NTFS','May 3 2010 9:14PM','May 3 2010 9:14PM','900 ' UNION ALL

    SELECT '911823','DC-SV-SQL1','E:\','231919841280','42710179840','189209661440','TransactionLogs','NTFS','May 3 2010 9:29PM','May 3 2010 9:29PM','900 ' UNION ALL

    SELECT '911876','DC-SV-SQL1','E:\','231919841280','42710179840','189209661440','TransactionLogs','NTFS','May 3 2010 9:44PM','May 3 2010 9:44PM','900 ' UNION ALL

    SELECT '911929','DC-SV-SQL1','E:\','231919841280','42716864512','189202976768','TransactionLogs','NTFS','May 3 2010 9:59PM','May 3 2010 9:59PM','900 ' UNION ALL

    SELECT '911982','DC-SV-SQL1','E:\','231919841280','42716864512','189202976768','TransactionLogs','NTFS','May 3 2010 10:14PM','May 3 2010 10:14PM','900 ' UNION ALL

    SELECT '912035','DC-SV-SQL1','E:\','231919841280','42716864512','189202976768','TransactionLogs','NTFS','May 3 2010 10:29PM','May 3 2010 10:29PM','900 ' UNION ALL

    SELECT '912088','DC-SV-SQL1','E:\','231919841280','42716864512','189202976768','TransactionLogs','NTFS','May 3 2010 10:44PM','May 3 2010 10:44PM','900 ' UNION ALL

    SELECT '912141','DC-SV-SQL1','E:\','231919841280','42716864512','189202976768','TransactionLogs','NTFS','May 3 2010 10:59PM','May 3 2010 10:59PM','900 ' UNION ALL

    SELECT '912194','DC-SV-SQL1','E:\','231919841280','42716864512','189202976768','TransactionLogs','NTFS','May 3 2010 11:14PM','May 3 2010 11:14PM','900 ' UNION ALL

    SELECT '912247','DC-SV-SQL1','E:\','231919841280','42724270080','189195571200','TransactionLogs','NTFS','May 3 2010 11:29PM','May 3 2010 11:29PM','900 ' UNION ALL

    SELECT '912300','DC-SV-SQL1','E:\','231919841280','42724270080','189195571200','TransactionLogs','NTFS','May 3 2010 11:44PM','May 3 2010 11:44PM','900 ' UNION ALL

    SELECT '912353','DC-SV-SQL1','E:\','231919841280','42739761152','189180080128','TransactionLogs','NTFS','May 3 2010 11:59PM','May 3 2010 11:59PM','900 '

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #myHostSpace OFF

  • May 31, 2010 at 8:18 am

    #1174514

    Scott awesome job posting the table and data; it makes it so easy for me to test with!

    Thank you!

    someone poke holes in my calculations, but i keep getting 20 days till full; here's the two different calculations i used to try and figure it out:

    --in theory, the calculation is simplys (totalspace divided by usedspace for an interval) times days in interval = days till full?

    --results 20.942569 days as DaysTillFull

    SELECT ((TotSpace * 1.0) / SpaceUsedPerinterval ) * TotalInterval as DaysTillFull

    FROM (

    select

    MAX(TotalSpaceBytes) as TotSpace,

    MIN(FreeSpaceBytes) As TotFree,

    MIN(UsedSpaceBytes) as Used,

    MAX(UsedSpaceBytes) as Used2,

    MAX(UsedSpaceBytes) - MIN(UsedSpaceBytes) As SpaceUsedPerinterval,

    DATEDIFF(day,Min(log_dt),Max(log_dt))+ 1 as TotalInterval

    from #myHostSpace ) myAlias

    --the freespace starting value

    with MyCTE1 AS (select TotalSpaceBytes,FreeSpaceBytes,UsedSpaceBytes,log_dt from #myHostSpace where log_dt IN( SELECT Min(log_dt) from #myHostSpace)),

    --the freespace ending value

    MyCTE2 AS (select TotalSpaceBytes,FreeSpaceBytes,UsedSpaceBytes,log_dt from #myHostSpace where log_dt IN( SELECT max(log_dt) from #myHostSpace))

    --in theory, it's (totalspace divided by usedspace) times days in interval = days till full?

    SELECT (MyCTE1.TotalSpaceBytes * 1.0) / (MyCTE2.UsedSpaceBytes - MyCTE1.UsedSpaceBytes) * (DATEDIFF(day,(MyCTE1.log_dt),(MyCTE2.log_dt))+ 1)

    FROM MyCTE1

    INNER JOIN MyCTE2 ON MyCTE1.log_dt < MyCTE2.log_dt

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 31, 2010 at 12:11 pm

    #1174573

    Thanks Lowell!

    Your strategy certainly gave me a few thinks to think about. I've been trying to break this out in steps to get the calculation right. Just a quick adjustment - I think that you should have used;

    SELECT ((TotFree * 1.0) / SpaceUsedPerinterval ) * TotalInterval as DaysTillFull

    and it would work out to around 3.65 days till full. I think your calculation assumes that the entire drive is empty. If you look at the data from beginning to end in the 3 days we used aprox. 31 GB, so 10 GB per day and we have 39.8 GB free.

    Also I was thinking about your use of MAX and MIN to get the SpaceUsedPerinterval. This assumes that the drive will be growing (less free space) over time. I don't think the calculation works if we had more free space over time. I think I should use the actual starting and ending values to calculate the SpaceUsedPerinterval.

    Thanks a lot - I was working on a huge script with several temp tables! If you have any other thoughts let me know.

Viewing 3 posts - 1 through 2 (of 2 total)

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