PerformanceCollection - - 86400 SQL2005FileSize.vbs $Target/Host/Property[Type="SQLServer3!Microsoft.SQLServer.DBEngine"]/ConnectionString$ $Target/Property[Type="SQLServer3!Microsoft.SQLServer.Database"]/DatabaseName$ ' Param 0: The SQL connection string for the server ' Param 1: The Database to use Option Explicit Const SQL_DISCOVERY_CONNECT_FAILURE = -1 Const SQL_DISCOVERY_QUERY_FAILURE = -2 if WScript.Arguments.Count = 2 then Dim oAPI Set oAPI = MOMCreateObject("MOM.ScriptAPI") 'Connect to the database Dim cnADOConnection Set cnADOConnection = MomCreateObject("ADODB.Connection") cnADOConnection.Provider = "sqloledb" cnADOConnection.ConnectionTimeout = 15 Dim sProv sProv = "Server=" & WScript.Arguments(0) & ";Database=" & WScript.Arguments(1) & ";Integrated Security=SSPI" cnADOConnection.Open sProv On Error Goto 0 If 0 <> Err.number then 'Error event to goin here call oAPI.LogScriptEvent("Get2005FileSize",42,1,"Connection Failed") Dim DBHealth DBHealth = SQL_DISCOVERY_CONNECT_FAILURE End If 'Connection established, now run the code Dim oResults Dim xCount Set oResults = cnADOConnection.Execute("SELECT [name],[physical_name],[size]/128.0 as TotalSizeInMB,FILEPROPERTY([name], 'SpaceUsed')/128.0 as SpaceUsedInMB,([size] - FILEPROPERTY([name], 'SpaceUsed'))/128.0 as FreeSpaceInMB,CASE WHEN is_percent_growth = 1 THEN CAST(growth as VARCHAR(8)) + '%' ELSE CAST(FLOOR(growth/128.0) as VARCHAR(8)) + 'MB' END as growth, [is_read_only] FROM sys.database_files ORDER BY [Name] ASC") Dim oBag Dim oBag2 Dim oBag3 Do While Not oResults.EOF Set oBag = oAPI.CreateTypedPropertyBag(2) call oBag.AddValue("DBName",Cstr(WScript.Arguments(1))) call oBag.AddValue("LogicalName" ,CStr(oResults(0))) call oBag.AddValue("TargetName","TotalSizeInMb") call oBag.AddValue("perfValue",Cstr(oResults(2))) oAPI.AddItem(oBag) Set oBag2 = oAPI.CreateTypedPropertyBag(2) call oBag2.AddValue("DBName",Cstr(WScript.Arguments(1))) call oBag2.AddValue("LogicalName" ,CStr(oResults(0))) 'call oBag.AddValue("PhysicalName",Cstr(oResults(1))) call oBag2.AddValue("TargetName","SpaceUsedInMb") call oBag2.AddValue("perfValue",Cstr(oResults(3))) oAPI.AddItem(oBag2) Set oBag3 = oAPI.CreateTypedPropertyBag(2) call oBag3.AddValue("DBName",Cstr(WScript.Arguments(1))) call oBag3.AddValue("LogicalName" ,CStr(oResults(0))) 'call oBag.AddValue("PhysicalName",Cstr(oResults(1))) call oBag3.AddValue("TargetName","FreeSpaceInMb") call oBag3.AddValue("perfValue",Cstr(oResults(4))) oAPI.AddItem(oBag3) oResults.MoveNext Loop cnADOConnection.Close Call oAPI.ReturnItems End If Function MomCreateObject(ByVal sProgramId) Set MomCreateObject = CreateObject(sProgramId) End Function 60 $Data/Property[@Name='DBName']$ $Data/Property[@Name='LogicalName']$ $Data/Property[@Name='TargetName']$ $Data/Property[@Name='perfValue']$ -