Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unhappy Unanswered: MS SQL Error 823 I/O Error

    Hi there,

    I am reinstall my OS (Win2k Server) and try to attach back my database to SQL Server (MS SQL 2000).

    After selecting MDF and LDF file. It come out with all those message below in my event viwer. And attach database action is fail.

    I had check with Microsoft and download the latest Server Pack 3, but still nothing is change. I am panic here.

    Can any one help me to solve this problem.

    Your help is very much appreciated.

    Thank you.

    Dave

    Message copy from Event Viwer.

    17052 :
    Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Log.LDF' may be incorrect.

    Error: 823, Severity: 24, State: 6
    I/O error (torn page) detected during read at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ultra_Log.LDF'.

    17207 :
    udopen: Operating system error 5(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Data.MDF.

    17204 :
    FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Data.MDF for virtual device number (VDN) 1.

    17052 :
    Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Data.MDF' may be incorrect.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Judging from the error messages, are you certain you have the files in the correct path? Just trying to eliminate the obvious, here.

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    Originally posted by MCrowley
    Judging from the error messages, are you certain you have the files in the correct path? Just trying to eliminate the obvious, here.
    Well.....i just copy back the file to C:\Program Files\Microsoft SQL Server\MSSQL\Data

    so.....i just using the utility in the Enterprise Manager call Attach Database....is something like sp_attach_db but in windows graphic mode...

    even i am using sp_attach_db command in command prompt, same mssg will appear....

    here by i submit my database file...the log file and the data file...
    please help to solve it....
    i have no idea about all this....

    thank you so much

    Dave
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check this link, it may be of relevance to your situation.

    Resore database with corrupted log file

  5. #5
    Join Date
    Sep 2003
    Posts
    3
    Hey....Thank a lot....
    even process in bertween a lot of error reported but it still work. Now i can access to my database now....

    Thanks. Very appreciated..


    We started out with only the MDF file. The log file had been corrupted and lost due to disk failure and we found out we weren't backing up the
    databases, only the file groups so we had a month of semi-useless backups.

    Definitions:
    <databasename> - this should be replaced, in any query detailed in the steps
    below, with the database being recovered.
    <database path> - this should be replaced with the path to the SQL data
    files.
    <database logfile> - this should be replaced with the filename of the SQL
    database log file minus the extenstion.

    1. In SQL Enterprise Manager, create a new database with the same name as
    the old one. Make it the same data file size or larger as the old MDF.

    2. Stop SQL Server.

    3. Delete the new MDF file and rename the old one back.

    4. Start SQL Server. The database should now show up tagged Suspect in
    Enterprise Manager.

    5. In SQL Query Analyser:
    use master
    go
    sp_configure 'allow', 1
    go
    reconfigure with override
    go
    update sysdatabases set status=32768 where name='<databasename>'
    go
    dbcc rebuild_log('<databasename>','<database path>\<database
    logfile>1.ldf')
    go

    NOTE: the second to last command is not a typo. The last variable of
    <databselogfile>1.ldf is correct. If your database was named CONTACTS this
    would give you CONTACTS_LOG1.ldf. Don't forget the '1' at the end.

    This should result in a message similar to, "Transaction log successfully
    rebuilt - transactional consistency lost."

    6. In SQL Query Analyzer:
    dbcc checkdb('<databasename>')

    This may result in a number of errors. If so, go to step 7. If there are no
    errors, the process may be complete. Check step 9.

    7. In SQL Query Analyzer:
    dbcc checkdb('<databasename>',repair_allow_data_loss)

    You may have to set the database to single user for this to work. It will
    alert you to this. This can be done in Enterprise Manager under the
    databases properties.

    8. Repeat steps 9 and 10 until step 9 results in no errors.

    9. The database may end up with a status of "dbo use only". To clear this,
    execute the following in SQL Query Analyzer:
    use master
    go
    exec sp_dboption '<databasename>', 'dbo use only', 'false'
    go

    You can also do this last step in Enterprise Manager under the databases
    properties.

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Quote Originally Posted by rdjabarov
    Check this link, it may be of relevance to your situation.

    Resore database with corrupted log file
    That works perfectly. There is a type however. The sp_configure command should read allow updates.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, with SQL 2005, you need to run an alter database command:

    Code:
    alter database databasename set emergency

  8. #8
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Today, it was a SQL 2K issue. RAID failure.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by DBA-ONE
    That works perfectly. There is a type however. The sp_configure command should read allow updates.
    It's not a typo. sp_configure does not need fully spelled reference to configuration settings like "allow updates" or "show advanced options".
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Didn't know that. I stand corrected.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is unusual - I can't offhand think of another system sproc that runs a SELECT if you don't provide parameters, and runs an UPDATE if you do.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2005
    Posts
    122
    Delete....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •