Home
  •  

AK545085

Monitor_AK545085 (UnitMonitor)

SQL Server missing update 2550552 to avoid deadlock involving filesystem and filestream driver

Knowledge Base article:

External

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

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.DBEngine
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityHigh
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server missing update 2550552 to avoid deadlock involving filesystem and filestream driver
<Details>
<Content>This instance of SQL Server is configured to allow FILESTREAM for Transact-SQL and Win32 streaming access. When operations like backup or anti-virus scan access files located in the filestream container concurrently when applications use the filestream data using the SqlFileStream API, you might encounter system level deadlocks. As a result operations like backup may hang. These deadlocks happen due to the filestream RsFx driver doing a flush operation on handle close. The resources involved in the deadlock are kernel level locks on internal file system components. You might need to reboot the entire system to recover from this problem. To prevent this problem from happening, apply the SQL Server update from KB article 2550552.
</Content>
<CollectedInformation>
<Info>
<Name>SQL Filestream access level</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>Current SQL Build</Name>
<Value>{1}</Value>
</Info>
<Info>
<Name>Recommended SQL Build</Name>
<Value>{2}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.0.0;

Source Code:

<UnitMonitor ID="Monitor_AK545085" 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="High" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessage57a2f105e3db484c941676430dd896a8">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

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

      <AlertParameter3>$Data/Context/Property[@Name='RecommendedSqlBuild']$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AK545085.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

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

      </Parameter>

      <Parameter>

        <Name>ProductVersionMajor</Name>

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

      </Parameter>

      <Parameter>

        <Name>ProductVersionMinor</Name>

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

      </Parameter>

      <Parameter>

        <Name>FileStreamAccessLevel</Name>

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

      </Parameter>

      <Parameter>

        <Name>CurrentSqlBuild</Name>

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

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString,$ProductVersionMajor,$ProductVersionMinor,$FileStreamAccessLevel,$CurrentSqlBuild)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

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

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

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

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

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



# Set up the output

$global:scriptoutput = new-object psobject

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

$scriptoutput | add-member NoteProperty "FileStreamAccessLevel" 0

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

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

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

# MAIN CODE SECTION

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



# Environment



$scriptenv = New-Object psobject

$scriptenv | Add-Member NoteProperty "RecommendedSqlBuild" ""



# Helper functions



# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{ 

	# 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

	$scriptenv.RecommendedSqlBuild = "00.00.0000.00"



	# Set parameter values

	# $scriptoutput.OutParam1 = Function1 $scriptargs.Param1

	$scriptoutput.CurrentSqlBuild = $scriptargs.CurrentSqlBuild

	$scriptoutput.FileStreamAccessLevel = $scriptargs.FileStreamAccessLevel

	$scriptoutput.RecommendedSqlBuild = $scriptenv.RecommendedSqlBuild

	

	# Detection Logic

	If (($scriptargs.ProductVersionMajor -eq 10) -and ($scriptargs.ProductVersionMinor -eq 50))

	{

		# now check if the filestream access level is set for Transact SQL and Win32 access 

		If ($scriptargs.FileStreamAccessLevel -eq 2)

		{

			# we need to check if this sql instance has the fix for this issue

			# the fix matrix is as follows 

			# sql release     affected builds                          Fix Build 

			# SQL 2008 R2 SP1 10.50.2500.0 &lt;= sql build &lt; 10.50.2769.0 10.50.2769.0 

			# SQL 2008 R2 RTM 10.50.1600.1 &lt;= sql build &lt; 10.50.1797.0 10.50.1797.0 

			# SQL 2008 SP3 fix will be released in future. At that time we will update the rule 

			# current sql build is available as a config point 

			if(([version]$scriptargs.CurrentSqlBuild -ge [version]"10.50.2500.0") -and ([version]$scriptargs.CurrentSqlBuild -lt [version]"10.50.2769.0"))

			{

				$scriptenv.RecommendedSqlBuild = "10.50.2769.0"

			}

			if(([version]$scriptargs.CurrentSqlBuild -ge [version]"10.50.1600.1") -and ([version]$scriptargs.CurrentSqlBuild -lt [version]"10.50.1797.0"))

			{

				$scriptenv.RecommendedSqlBuild = "10.50.1797.0"

			}

			if ([version]$scriptenv.RecommendedSqlBuild -ne [version]"00.00.0000.00")

			{

				#Raise alert

				$scriptoutput.RecommendedSqlBuild = $scriptenv.RecommendedSqlBuild

				$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.FileStreamAccessLevel -ne $null)

{

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

}



if ($scriptoutput.CurrentSqlBuild -ne $null)

{

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

}



if ($scriptoutput.RecommendedSqlBuild -ne $null)

{

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

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</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>