SQL Server Performance

assertion/I/O errors

Discussion in 'Performance Tuning for DBAs' started by tulcanla, Oct 8, 2003.

  1. tulcanla New Member

    I've got 2 errors:<br /><br />1 SQL Server Assertion: File: &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />:sql
    tdbmsstorengdrsinclude
    ecord.inl&gt;, line=1447 <br />Failed Assertion = 'm_SizeRec &gt; 0 && m_SizeRec &lt;= MAXDATAROW'.<br /><br /> and then <br /><br />2 I/O error (bad page ID) detected during read at offset 0x000000bf074000 in file 'f:SQL data filesdata filesdw_prod.mdf'..<br /><br /> do you know about this?<br /><br /> Please help<br /><br />Thanks <br /><br />lmt
  2. Luis Martin Moderator

  3. satya Moderator

    I believe this error is normally attributed to hardware failure. It could HDD, controller, faulty device drivers etc.

    Suggestion is to check thru the hardware thoroughly and restore from good backup source.

    And not mentioned which version of SQL is used, check this KBAhttp://support.microsoft.com/defaul...port/kb/articles/q281/8/09.ASP&NoWebContent=1 for more infromation.



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. tulcanla New Member

    Thanks all.

    This type of error repeated itself almost always after a tempdb log or a datafile run out of space.

    because I have a bcp database type(data warehouse) and I do load a lots of data I do not want the log to go up.

    It is possible that the bug in sql 7 persisted in sql2000.

    I will keep an eye openned on my file sizes and I will see if it pops up again

    respectfully

    LMT
  5. satya Moderator

    You can use BULK-LOGGED recovery model during this load and set back to FULL RECOVERY MODEL and if you're using ver.7 then can set Tlog to Trunc.log at chkpt mode.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. ykchakri New Member

    I think he is talking about filling up his data file. And I think setting up Recovery model can't help you filling up data file. And Recovery model can't be setup for tempdb to control it's log file. Make sure you have enough disk space for the files. Or you can also add additional files to the database to allow the database to extend into additional disks.
  7. satya Moderator

    You will get this type of assertion errors and when there is no space on Tlog file also, so keeping in that in mind I suggested about recovery model.

    By default if the data file is kept for auto-growth then there is no chance of getting this error.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. tulcanla New Member

    I have my eyes wide open on my loading schedule.<br />[}<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />And I will try to change the mode after the load is done.<br /><br />thanks<br /><br />lmt<br /><br />
  9. tulcanla New Member

    -- we have changed one of the disks that contains the main file and till now(knock in wood) no error.<br /><br />-- I hope this nightmare will stop here<br />[<img src='/community/emoticons/emotion-6.gif' alt=':(' />!]
  10. thomas New Member

    I have had this exact same problem on a server which has no hardware problems. No full data or log files either. It's not easily reproducable and is intermittent. I haven't followed all the steps in the KB article Satya posted yet but it strikes me that there aren't any solutions in the article - only more diagnosis tools.

    Tom Pullen
    DBA, Oxfam GB
  11. sklalit New Member

    I got the same problem of assertion while updating or inserting particular one table in database even I have intalled SP3.

    Lalit
  12. satya Moderator

    Any other information on the error log during this behaviour.
    Refer through the KBA above for diagnosing the issue.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. sklalit New Member

    Error: 3624, Severity: 20, State: 1. follows the assertion error and connection is broken from my vb App. When i restart the application it works fine but when again it tries to update the particular table again same problem arises.
    >Lalit
  14. satya Moderator

    Try to reapply SP3a to fix the assertion errors.

    Under the following conditions, a client can receive error 3624 (retail assertion) and the client's connection to SQL Server is terminated: • A data modification query contains a subquery.
    • The subquery contains a DISTINCT clause.
    • The subquery references a view that also contains a DISTINCT clause.
    • The query is run with a nested loop join.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. sklalit New Member

    We installed SP3. Are SP3 and SP3a same thing or different?
    >
    >One more thing i noticed, Acttually this problem is in my client database and i wonder that the space available for the database is zero. Could it be the reason for the same.


    Lalit
  16. satya Moderator

    SP3a has got few more fixes that weren't fixed in SP3 and the SP3a is only available right now.

    Well, you must check for the free space available on the database before diagnosing any other issue.
    Run SP_SPACEUSED against that database see the space available.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  17. sklalit New Member

    Even after increasing space the problem not solved.

    But when we droped the specific tabe and created new one then problem seems to be resolved. Was it hardware problem?

    But thanks Satya for your valuable suggestion.



    Lalit
  18. satya Moderator

    As the free psace is solved and still problem persits then I will incline towards point its a hardware related, as the troubled table is dropped and recreated the mismatched pages are corrected.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page