Home
  •  

Sql Encountered 823 Error

AK62627.IOError823 (Rule)

SQL Encountered 823 Error indicating IO error.

Knowledge Base article:

External

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

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.Database
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityError
Alert PriorityHigh
RemotableTrue
Alert Message
SQL Server has encountered a database I/O error: Msg 823

<Details>
<Content>Msg 823 was logged in the Windows Application Event Log. This error can indicate a database consistency problem. SQL Server generates an 823 error when an I/O operation on the database or transaction log fails. In some cases, the failure might be due to other database consistency problems. The operating system error that caused the I/O to fail is provided in the message.

Consider running a full database consistency check (DBCC CHECKDB) to determine the extent of all consistency errors. See the Information collected below for the database file context for which this error was detected. Because Msg 823 could be related to an I/O problem, you should review the Windows System Event Log for indication of I/O problems and carefully inspect all I/O hardware and system software components.</Content>
<CollectedInformation>
<Info>
<Name>Database file reported for this error</Name>
<Value>{0}</Value>
</Info>
<Info>
<Name>Operating system error</Name>
<Value>{1}</Value>
</Info>
</CollectedInformation>
</Details>

Member Modules:

ID Module Type TypeId RunAs 
source DataSource Microsoft.KnowledgeServices.Library.EventAndScript Default
filter ConditionDetection System.ExpressionFilter Default
alert WriteAction System.Health.GenerateAlert Default

Source Code:

<Rule ID="AK62627.IOError823" 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>^823$</EventIDRegEx>

      <ScriptName>IOError823.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 1 (Operating System Error)      

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



      Option Explicit



      Dim oArgs

      Set oArgs = WScript.Arguments



      Dim databaseId, sqlInstance      

      Dim oAPI, oBag

      

      If oArgs.Count &lt; 4 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)                

      

      '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 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[1]$" "$Data/Params/Param[4]$"</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="AlertMessageID53b8066d61584ec3873ee36422bcaf74"]$</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.10</Custom10>

    </WriteAction>

  </WriteActions>

</Rule>