SQL 2008 DB Size Provider

Microsoft.SQLServer.2008.DBSizeRawPerfProvider (DataSourceModuleType)

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2008.DBSizeRawPerfProvider" Accessibility="Internal" RunAs="SQL!Microsoft.SQLServer.SQLProbeAccount">

  <Configuration>

    <xsd:element name="IntervalSeconds" type="xsd:integer"/>

    <xsd:element name="SyncTime" type="xsd:string"/>

    <xsd:element name="ConnectionString" type="xsd:string"/>

    <xsd:element name="ObjectName" type="xsd:string"/>

    <xsd:element name="CounterName" type="xsd:string"/>

    <xsd:element name="InstanceName" type="xsd:string"/>

    <xsd:element name="DatabaseName" type="xsd:string"/>

    <xsd:element name="Value" type="xsd:string"/>

    <xsd:element name="TimeoutSeconds" type="xsd:int"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="IntervalSeconds" ParameterType="int" Selector="$Config/IntervalSeconds$"/>

    <OverrideableParameter ID="SyncTime" ParameterType="string" Selector="$Config/SyncTime$"/>

    <OverrideableParameter ID="TimeoutSeconds" ParameterType="int" Selector="$Config/TimeoutSeconds$"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="DS" TypeID="Windows!Microsoft.Windows.TimedScript.PropertyBagProvider">

          <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>

          <SyncTime>$Config/SyncTime$</SyncTime>

          <ScriptName>GetSQL2008DBSpace.js</ScriptName>

          <Arguments>$Config/ConnectionString$</Arguments>

          <ScriptBody><Script>

//Copyright (c) Microsoft Corporation. All rights reserved.



// Arguments (1)

// Arg 0: SQL Connection String



// Outputs a property bag for each database containing DB and Log Free space in MB and Percentage





var ScriptName = "Get SQL 2008 Database Space";



// Failure Conditions

var SCRIPT_ARG_FAILURE = -1;

var SCRIPT_ARG_FAILURE_MSG = "Invalid arguments passed to script";

var SQL_CONNECT_FAILURE = -2;

var SQL_CONNECT_FAILURE_MSG = "Could not connect to SQL";

var SQL_QUERY_FAILURE = -3;

var SQL_QUERY_FAILURE_MSG = "Failed to query databasize size information";

var SQL_DBLIST_FAILURE = -4;

var SQL_DBLIST_FAILURE_MSG = "Failed to enumerate databases";

var OPSMGR_API_FAILURE = -5;

var OPSMGR_API_FAILURE_MSG = "Failed to create Operations Manager scripting API object";



// Database States

var DB_EMERGENCYMODE = 32768

var DB_LOADING = 22

var DB_NORMAL = 0

var DB_OFFLINE = 512

var DB_RECOVERING = 192

var DB_STANDBY = 1024

var DB_SUSPECT = 256





var ScriptArgs = WScript.Arguments;



if (ScriptArgs.Length == 1)

{

	GetDBSize(ScriptArgs(0));

}



function GetDBSize(SQLConnectionString)

{

	var SQLServer;

	try

	{

		SQLServer = GetDMOServer(SQLConnectionString);

	}

	catch (e)

	{

		PrintError(e);

		FailScript(e, SQL_CONNECT_FAILURE_MSG);

	}



	var opsmgrAPI;

	try

	{

		opsmgrAPI = new ActiveXObject("MOM.ScriptAPI");

	}

	catch (e)

	{

		PrintError(e);

		FailScript(e, OPSMGR_API_FAILURE_MSG);

	}

	// This loop runs SQLServer.Databases.Count + the number of deleted databases BETWEEN dbs starting from id 1 to the last

	// database which is not deleted/detached times.

	var delDbCount = 0;

	

	for (i=1; i &lt;= SQLServer.Databases.Count + delDbCount; i++)

	{

		try

		{

			var currentDB = SQLServer.Databases.ItemByID(i);

		}

		catch (e)

		{

			// If database is deleted/detached just move to the next

			delDbCount = delDbCount + 1;

			continue;



		}





		if ((currentDB.Status == DB_NORMAL ) || (currentDB.Status == DB_STANDBY))

		{

			var propertyBag = opsmgrAPI.CreateTypedPropertyBag(2);

			var logSize, logAvailable, dbSize, dbAvailable;

			logSize = currentDB.TransactionLog.Size;

			

			if (logSize &lt; 1)

			{

				logAvailable = 0;

			}

			else

			{

				logAvailable = currentDB.TransactionLog.SpaceAvailableInMB;

			}



			dbSize = currentDB.Size - logSize 

			dbAvailable = currentDB.SpaceAvailableInMB - logAvailable

			propertyBag.AddValue("Database", currentDB.Name);

			propertyBag.AddValue("TransactionLogSize", logSize);

			//propertyBag.AddValue("TransactionLogUsed", logSize - logAvailable);

			propertyBag.AddValue("TransactionLogFree", logAvailable);			



			if (logAvailable == 0) 

			{

				propertyBag.AddValue("TransactionLogPercentFree", 0);

				//propertyBag.AddValue("TransactionLogUsedPercent", 100 );

			}

			else

			{

				propertyBag.AddValue("TransactionLogPercentFree", 100 * logAvailable / logSize);

				//propertyBag.AddValue("TransactionLogUsedPercent", 100 * (logSize - logAvailable) / logSize);

			}

			

		

			

				propertyBag.AddValue("DBSize", dbSize);

				//propertyBag.AddValue("DBUsed", dbSize - dbAvailable);

				propertyBag.AddValue("DBFree", dbAvailable);

				propertyBag.AddValue("DBPercentFree", 100 * dbAvailable / dbSize);

				//propertyBag.AddValue("DBUsedPercent", 100 * (dbSize - dbAvailable) / dbSize);

			

			opsmgrAPI.AddItem(propertyBag);

		}

	}

	opsmgrAPI.ReturnItems();

			

	SQLServer.Close();

		



}



function GetDMOServer(SQLConnectionString)

{

	if (SQLConnectionString != "")

	{

		var SQLServer = new ActiveXObject("SQLDMO.SQLServer");

		SQLServer.LoginSecure = true;

		SQLServer.Connect(SQLConnectionString);

		return SQLServer;

	}

	else

	{

		return null;

	}

		

}



function FailScript(error, sMessage)

{

  var opsmgrAPI;



  opsmgrAPI = new ActiveXObject("MOM.ScriptAPI");



  opsmgrAPI.LogScriptEvent("GetSQL2008DBSpace.js", 4000, 1, sMessage &amp; ". " &amp; error.m_sDescription);





}





function PrintError(error)

{

    WScript.Echo("============================= BEGIN SCRIPT ERROR =================================\n");

    WScript.Echo("Script Name           : " + ScriptName);

    WScript.Echo("Error Type        : " + error);

    WScript.Echo("Error Number      : " + error.number);

    WScript.Echo("Error Code        : " + (error.number &amp; 0xFFFF));

    WScript.Echo("Win32 Facility    : " + (error.number&gt;&gt;16 &amp; 0x1FFF));

    WScript.Echo("Error Source      : " + error.Source);

    WScript.Echo("Error Description : " + error.description);

    WScript.Echo("============================== END SCRIPT ERROR ==================================\n");

}

                

</Script></ScriptBody>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

        </DataSource>

        <ConditionDetection ID="PerfMapper" TypeID="SystemPerf!System.Performance.DataGenericMapper">

          <ObjectName>$Config/ObjectName$</ObjectName>

          <CounterName>$Config/CounterName$</CounterName>

          <InstanceName>$Config/InstanceName$</InstanceName>

          <Value>$Config/Value$</Value>

        </ConditionDetection>

        <ConditionDetection ID="InstanceFilter" TypeID="System!System.ExpressionFilter">

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery Type="String">InstanceName</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value Type="String">$Config/DatabaseName$</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

        </ConditionDetection>

      </MemberModules>

      <Composition>

        <Node ID="InstanceFilter">

          <Node ID="PerfMapper">

            <Node ID="DS"/>

          </Node>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>SystemPerf!System.Performance.Data</OutputType>

</DataSourceModuleType>