Home
  •  

AK63308

Monitor_AK63308 (UnitMonitor)

SQL Server missing update KB974130 to avoid linked server usage related memory leak

Knowledge Base article:

External

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

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.DBEngine
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 missing update KB974130 to avoid linked server usage related memory leak
<Details>
<Content>You are missing the SQL Server update KB974130. This update addresses known memory leak issues when using linked servers. If you use linked servers heavily and access data on the remote server with sql_variant data type, memory is leaked for each execution that corresponds to the sql_variant output parameter or sql_variant data rows returned from the remote server. If this process continues, you can encounter out-of-memory errors. Please review the KB 974130 and apply the required updates.</Content>
<CollectedInformation>
<Info>
<Name>SQL Server instance current version</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>SQL Server update version recommended</Name>
<Value>{1}</Value>
</Info>
<Info>
<Name>Number of linked servers defined in this SQL server instance</Name>
<Value>{2}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

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

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessagec93e730919e44a68bcc2afef7d742cdd">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/ProductVersion$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='RecommendedUpdateVersion']$</AlertParameter2>

      <AlertParameter3>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/LinkedServersCount$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AK63308.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>MinorVersion</Name>

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

      </Parameter>

      <Parameter>

        <Name>BuildNumber</Name>

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

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($MinorVersion,$BuildNumber)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

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

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



# Set up the output

$global:scriptoutput = new-object psobject

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

function AdvisorRule($scriptargs, $scriptoutput)

{

    $minorVersion = $scriptargs.MinorVersion

    $buildNumber = $scriptargs.BuildNumber

    

    if ($minorVersion -eq 50)

    {

        $scriptoutput.RecommendedUpdateVersion = "10.50.1702.0"

    }

    elseif ($minorVersion -eq 0)

    {

        if ($buildNumber -lt 1823)

        {

            $scriptoutput.RecommendedUpdateVersion = "10.00.1823"

        }

        else

        {

            $scriptoutput.RecommendedUpdateVersion = "10.00.2746"

        }

    }

    else

    {

        $scriptoutput.RecommendedUpdateVersion = "NA"

    }

}



AdvisorRule $scriptargs $scriptoutput



# set the output

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

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.RecommendedUpdateVersion -ne $null)

{

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

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <And>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/LinkedServersCount$</Value>

            </ValueExpression>

            <Operator>Greater</Operator>

            <ValueExpression>

              <Value Type="Integer">0</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/MajorVersion$</Value>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Integer">10</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <Or>

            <Expression>

              <And>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/MinorVersion$</Value>

                    </ValueExpression>

                    <Operator>Equal</Operator>

                    <ValueExpression>

                      <Value Type="Integer">0</Value>

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

                <Expression>

                  <Or>

                    <Expression>

                      <And>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>Equal</Operator>

                            <ValueExpression>

                              <Value Type="Integer">1600</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildRevisionNumber$</Value>

                            </ValueExpression>

                            <Operator>GreaterEqual</Operator>

                            <ValueExpression>

                              <Value Type="Integer">22</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                      </And>

                    </Expression>

                    <Expression>

                      <And>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>Greater</Operator>

                            <ValueExpression>

                              <Value Type="Integer">1600</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>Less</Operator>

                            <ValueExpression>

                              <Value Type="Integer">1823</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                      </And>

                    </Expression>

                    <Expression>

                      <And>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>GreaterEqual</Operator>

                            <ValueExpression>

                              <Value Type="Integer">2531</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>Less</Operator>

                            <ValueExpression>

                              <Value Type="Integer">2746</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                      </And>

                    </Expression>

                  </Or>

                </Expression>

              </And>

            </Expression>

            <Expression>

              <And>

                <Expression>

                  <SimpleExpression>

                    <ValueExpression>

                      <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/MinorVersion$</Value>

                    </ValueExpression>

                    <Operator>Equal</Operator>

                    <ValueExpression>

                      <Value Type="Integer">50</Value>

                    </ValueExpression>

                  </SimpleExpression>

                </Expression>

                <Expression>

                  <Or>

                    <Expression>

                      <And>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>Equal</Operator>

                            <ValueExpression>

                              <Value Type="Integer">1600</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildRevisionNumber$</Value>

                            </ValueExpression>

                            <Operator>GreaterEqual</Operator>

                            <ValueExpression>

                              <Value Type="Integer">1</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                      </And>

                    </Expression>

                    <Expression>

                      <And>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>Greater</Operator>

                            <ValueExpression>

                              <Value Type="Integer">1600</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                        <Expression>

                          <SimpleExpression>

                            <ValueExpression>

                              <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                            </ValueExpression>

                            <Operator>Less</Operator>

                            <ValueExpression>

                              <Value Type="Integer">1702</Value>

                            </ValueExpression>

                          </SimpleExpression>

                        </Expression>

                      </And>

                    </Expression>

                  </Or>

                </Expression>

              </And>

            </Expression>

          </Or>

        </Expression>

      </And>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <And>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/LinkedServersCount$</Value>

                </ValueExpression>

                <Operator>Greater</Operator>

                <ValueExpression>

                  <Value Type="Integer">0</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <SimpleExpression>

                <ValueExpression>

                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/MajorVersion$</Value>

                </ValueExpression>

                <Operator>Equal</Operator>

                <ValueExpression>

                  <Value Type="Integer">10</Value>

                </ValueExpression>

              </SimpleExpression>

            </Expression>

            <Expression>

              <Or>

                <Expression>

                  <And>

                    <Expression>

                      <SimpleExpression>

                        <ValueExpression>

                          <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/MinorVersion$</Value>

                        </ValueExpression>

                        <Operator>Equal</Operator>

                        <ValueExpression>

                          <Value Type="Integer">0</Value>

                        </ValueExpression>

                      </SimpleExpression>

                    </Expression>

                    <Expression>

                      <Or>

                        <Expression>

                          <And>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>Equal</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">1600</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildRevisionNumber$</Value>

                                </ValueExpression>

                                <Operator>GreaterEqual</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">22</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                          </And>

                        </Expression>

                        <Expression>

                          <And>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>Greater</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">1600</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>Less</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">1823</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                          </And>

                        </Expression>

                        <Expression>

                          <And>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>GreaterEqual</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">2531</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>Less</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">2746</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                          </And>

                        </Expression>

                      </Or>

                    </Expression>

                  </And>

                </Expression>

                <Expression>

                  <And>

                    <Expression>

                      <SimpleExpression>

                        <ValueExpression>

                          <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/MinorVersion$</Value>

                        </ValueExpression>

                        <Operator>Equal</Operator>

                        <ValueExpression>

                          <Value Type="Integer">50</Value>

                        </ValueExpression>

                      </SimpleExpression>

                    </Expression>

                    <Expression>

                      <Or>

                        <Expression>

                          <And>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>Equal</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">1600</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildRevisionNumber$</Value>

                                </ValueExpression>

                                <Operator>GreaterEqual</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">1</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                          </And>

                        </Expression>

                        <Expression>

                          <And>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>Greater</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">1600</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                            <Expression>

                              <SimpleExpression>

                                <ValueExpression>

                                  <Value Type="Integer">$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/BuildNumber$</Value>

                                </ValueExpression>

                                <Operator>Less</Operator>

                                <ValueExpression>

                                  <Value Type="Integer">1702</Value>

                                </ValueExpression>

                              </SimpleExpression>

                            </Expression>

                          </And>

                        </Expression>

                      </Or>

                    </Expression>

                  </And>

                </Expression>

              </Or>

            </Expression>

          </And>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>