Home
  •  

AK555349

Monitor_AK555349 (UnitMonitor)

SQL Server database file might not grow using the configured growth value

Knowledge Base article:

External

http://go.microsoft.com/fwlink/?LinkId=231432

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.Database
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server database file might not grow using the configured growth value
<Details>
<Content>This SQL Server database has one or more transaction log files whose auto-grow setting is configured to grow by 4GB increments. There is a known issue in SQL Server where this specific auto-grow increment value is not honored and instead the transaction log grows in much smaller increments [like 250 KB]. Review the list of files shown in the information section and change their auto-grow settings to a value other than 4GB.</Content>
<CollectedInformation>
<Info>
<Name>Logical Name of files affected by this problem</Name>
<Value>{0}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AK555349" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessageb34fa567fb894944940c4af5269c6455">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='AffectedFileName']$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AK555349.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

        <Value>$Target/Host/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

      <Parameter>

        <Name>SQLServerVersion</Name>

        <Value>$Target/Host/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/Version$</Value>

      </Parameter>

      <Parameter>

        <Name>IsDatabaseReadOnly</Name>

        <Value>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database"]/ReadOnly$</Value>

      </Parameter>

      <Parameter>

        <Name>TargetDatabaseID</Name>

        <Value>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database"]/DatabaseId$</Value>

      </Parameter>

      <Parameter>

        <Name>TargetDataBaseStatus</Name>

        <Value>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database"]/Status$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString,$SQLServerVersion,$IsDatabaseReadOnly,$TargetDatabaseID,$TargetDataBaseStatus)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "ConnectionString" $ConnectionString

$scriptargs | add-member NoteProperty "SQLServerVersion" $SQLServerVersion

$scriptargs | add-member NoteProperty "IsDatabaseReadOnly" $IsDatabaseReadOnly

$scriptargs | add-member NoteProperty "TargetDatabaseID" $TargetDatabaseID

$scriptargs | add-member NoteProperty "TargetDataBaseStatus" $TargetDataBaseStatus



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

$scriptoutput | add-member NoteProperty "AffectedFileName" ""

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment



$scriptenv = new-object psobject

$scriptenv | add-member NoteProperty "ConnectionString" ""

$scriptenv | add-member NoteProperty "QueryString" ""

$scriptenv | add-member NoteProperty "SQLServerVersion" ""

$scriptenv | add-member NoteProperty "ErrorFlag" $false





# Function to get affected File Name



function Get-AffectedFileName {



	$scriptenv.QueryString = "select name from sys.master_files where database_id = " + $scriptargs.TargetDatabaseID + " AND type = 1 AND is_percent_growth = 0 AND growth % 524288 = 0"

	

    $scriptenv.ConnectionString = "Data Source=" + $scriptargs.ConnectionString + "; Initial Catalog=master;Integrated Security=SSPI"

    $conn = new-object System.Data.SqlClient.SqlConnection($scriptenv.ConnectionString)

    $conn.Open()

    $cmd = $conn.CreateCommand()

	

	$cmd.CommandText = $scriptenv.QueryString

	$sqlReader= $cmd.ExecuteReader()

	while ($sqlReader.Read()){

		If($sqlReader[0] -ne $null -and $sqlReader[0].Trim() -ne ""){

			$scriptoutput.AffectedFileName = $scriptoutput.AffectedFileName + "," + $sqlReader[0] 

		}

	}

	$sqlReader.Close()

	If($scriptoutput.AffectedFileName.Length -gt 0)

	{

		$scriptoutput.AffectedFileName = $scriptoutput.AffectedFileName.Substring(1)

	}

}



# Main function



function AdvisorRule($scriptargs, $scriptoutput) 

{ 

	trap [Exception] { 

		$scriptenv.ErrorFlag = $true

		continue

	}

	

	# All parameters should be populated outside of the main function. 

	# The mian function should only include the detection logic so that it can be easyly reused by the Atlanta authoring tool.

	

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.AffectedFileName = ""



	# Set parameter values

	$scriptenv.SQLServerVersion = $scriptargs.SQLServerVersion.SubString(0,$scriptargs.SQLServerVersion.Indexof('.'))

	

	# Detection Logic

	

	# This rule is only applicable for SQL Server 2008 and SQL Server 2008 R2

	If ($scriptenv.SQLServerVersion -eq "10"){

		# Evaluate this rule only if the Database property is_read_only = 0 and state = ONLINE

		If($scriptargs.TargetDataBaseStatus.ToUpper() -eq "ONLINE" -and $scriptargs.IsDatabaseReadOnly -eq $false){

			Get-AffectedFileName

			If($scriptoutput.AffectedFileName -ne "" -and $scriptoutput.AffectedFileName -ne $null){

				If ($scriptenv.ErrorFlag -ne $true){

					#Raise alert

					$scriptoutput.HasIssue = $true

				}

			}

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



if ($scriptoutput.AffectedFileName -ne $null)

{

	$bag.AddValue("AffectedFileName", $scriptoutput.AffectedFileName)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86396</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>