Sql Encountered 17207 Error

AK216930.DBError17207 (Rule)

SQL Server has encountered a database availability error: Msg 17207

Knowledge Base article:

Element properties:

Member Modules:

Source Code:

<Rule ID="AK216930.DBError17207" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database" ConfirmDelivery="true" Remotable="true" Priority="Normal" DiscardLevel="100">

  <Category>Alert</Category>

  <DataSources>

    <DataSource ID="source" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.EventAndScript">

      <ComputerName>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

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

      <EventIDRegEx>^17207$</EventIDRegEx>

      <ScriptName>DBError17207.vbs</ScriptName>

      <ScriptBody><Script>

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

      ' This script takes the following parameters

      ' Param 0: the database id

      ' Param 1: The SQL connection string to connect to

      ' Param 2: Event Log Param 2 (Operating System Error)      

      ' Param 3: Event Log Param 3 (Database file name) 

      ' Param 4: Event Log Param 1 (FCB data) 



 			Option Explicit



      Dim oArgs

      Set oArgs = WScript.Arguments

      

      Dim databaseId, sqlInstance

      Dim oAPI, oBag

      

      If oArgs.Count &lt; 5 Then

        Wscript.Quit -1

      End If

      

      Set oAPI = CreateObject("MOM.ScriptAPI")

      Set oBag = oAPI.CreatePropertyBag()



      databaseId = oArgs(0)      

      sqlInstance = oArgs(1)

      

      Call oBag.AddValue("OperatingSystemError", oArgs(2))



      Dim databaseFile

      databaseFile = oArgs(3)

      Call oBag.AddValue("DatabaseFile", databaseFile)                

      

      Dim strFCB

      strFCB = oArgs(4)

      

      'now run query to determine database files             

      Dim connString

      connString = "Provider=SQLOLEDB;Data Source=" + sqlInstance + "; Initial Catalog=master;Integrated Security=SSPI"



      Dim oConnection

      Set oConnection = CreateObject("ADODB.Connection")

      oConnection.Open connString



      Dim query

      ' Note: In VBScript, you use ? to pass parameters. 

      query = "select physical_name from sys.master_files where database_id = ? and physical_name like ? "

      

      Dim oCommand

      Set oCommand = CreateObject("ADODB.Command")

      oCommand.CommandText = query      

      oCommand.CommandType = 1 'adCmdText

      

      Dim param

      ' Note: 3 =&gt; adInteger, 1 =&gt; adParamInput

      Set param = oCommand.CreateParameter("databaseId", 3, 1) 

      oCommand.Parameters.Append param

      param.Value = databaseId	

      

      ' Note: 202 =&gt; adVarWChar (for NVarChar), 1 =&gt; adParamInput

      Set param = oCommand.CreateParameter("databaseFile", 202, 1, 512) 

      oCommand.Parameters.Append param

      param.Value = "%" + EscapeSQLString(databaseFile) + "%"	



      oCommand.ActiveConnection = oConnection



	  	Dim oResults

      Set oResults = oCommand.Execute(query)     



      If NOT oResults.EOF And strFCB &lt;&gt; "FCB::RemoveAlternateStreams" Then

         Call oBag.AddValue("FileMatch", "Yes")

      Else

         Call oBag.AddValue("FileMatch", "No")

      End If

            

      Call oAPI.Return(oBag)

      WScript.Quit() 

            

      ' Escapes the parameter string

      Function EscapeSQLString(param)          

          param = Replace(param, "[", "[[]")

          param = Replace(param, "%", "[%]")

          param = Replace(param, "_", "[_]")

          EscapeSQLString = param

      End Function

  </Script></ScriptBody>

      <ScriptTimeout>30</ScriptTimeout>

      <ScriptArguments>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.Database"]/DatabaseId$ $Target/Host/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$ "$Data/Params/Param[2]$" "$Data/Params/Param[3]$" "$Data/Params/Param[1]$"</ScriptArguments>

    </DataSource>

  </DataSources>

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

    <Expression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="String">Property[@Name='FileMatch']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="String">Yes</Value>

        </ValueExpression>

      </SimpleExpression>

    </Expression>

  </ConditionDetection>

  <WriteActions>

    <WriteAction ID="alert" TypeID="Health!System.Health.GenerateAlert">

      <Priority>2</Priority>

      <Severity>2</Severity>

      <AlertMessageId>$MPElement[Name="AlertMessageID54b4056d61584ec3873ee36422bcaf74"]$</AlertMessageId>

      <AlertParameters>

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

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

      </AlertParameters>

      <Suppression>

        <SuppressionValue>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.Database"]/DatabaseName$</SuppressionValue>

        <SuppressionValue>$Target/Host/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.ServerRole"]/InstanceName$</SuppressionValue>

      </Suppression>

      <Custom1>Administration\Disaster Recovery, Backup/Restore, Attach/Detach,  DB corruption</Custom1>

      <Custom2/>

      <Custom3/>

      <Custom4/>

      <Custom5/>

      <Custom6/>

      <Custom7/>

      <Custom8/>

      <Custom9/>

      <Custom10>1.0.0.4</Custom10>

    </WriteAction>

  </WriteActions>

</Rule>