HELP! Database crahsed!
-
I have a newly created database, no backup has been set up yet. lots of tables/stored procedures/Functions have been created, the database creashed this morning for unknown reason, with the error message:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x000000001b4000 in file 'C:\Inetpub\wwwroot\SCReviewV3\DB\SCReviewV3_Data.MDF'.
Connection Broken
Can any one help?
I just need to recover the table structure and stored procedures/functions, I don't care the data.
Thanks.
-
Run DBCC CHECKDB against the database. Correct any errors that it reports (by using the repair option). If this does not report any errors, it is most likely an intermitant hardware problem. Check your server's system log for hardware related errors. Let us know the results. You may be up the creek on this one as most I/O page errors result in you having to implement your recovery strategy. No backup = no recovery.
-
I recovered the database using a tool called "Recovery for SQL Server".
However, problem continues and I am able to reproduce a different crash:
I have a sp which is to delete a record, to delete the record, I check the row version. If the row version is wrong, it shouldn't be deleted.
Here is the problem:
My front end code passed a wrong value to the row version parameter. The rowversion is a timestamp type, I passed a Byte() to it. This caused the database crashed, I have to restart the server to connect to the database.
I think there is sort of buffer overflow in the system.
-
(This post was modified)
This is the advice if the database is not online:
Run sp_detachdb for your database, copy database files and try to re-attach to the same or different server. If it is a HW problem then copying may theoretically help. I am not sure you will be able to run DBCC CHECKDB is the database is not online. What is the status that is reported for the database in Enterprise Manager?
Also: Backup the database first thing. After that script the whole database so you will be able to recreate your database if needed. I would advise to script without DROP option.
Another note: I would not place database files under wwwroot folder.
Regards,Yelena Varsha
-
You may be correct, but I would still recommend watching your system log for other hardware related messages as your original message references a bad page file (which is in disk, not in buffer) in your MDF file. Does this rowversion problem produce the same error upon crashing the database?
Also, take a backup of your DB
-
There is no hardware related crash.
I am not sure if it is the rowversion causing the same error upon crashing database, I assume yes.
What I am thinking is: couldn't this be used as a sort of hacking? the system crashed by just passing a wrong data in the stored procedure! I can't believe it.
Yes back up is important, it should be set up at the very beginning of creating a new database.
-
DBCC CHECKDB is no use because the database is offline already.
Detach/attach was not working. That tool "Recovery for SQL Server" saved me.
-
Do you want to check in the database / log properties what is the Database Growth. If it fixed and there is no free space and the size increases due to the wrong value, theoretically the error message should be different, something about free space not available, but practically anything could happen.
Also as I said, it is not a good idea to put database files under the web server wwwroot folder because of file access permissions. Try to copy this database into a different location, outside of Inetpub, attach as a different database name and try from there if you will have the same problem.
I do have a somewhat similar problem with MS ACCESS database residing in a virtual directory that I can not open in the shared mode on the web server due to account permission issues. There is no problems connecting to the very same database if it is in different location or on a different web server.
Regards,Yelena Varsha
-
Hi,
This is Minaz Amin.
Please check the system event log for any hardware issue.
I would like to know what is version of the SQL Server(is it SQL Server 2000 ?) and If the SQL Server is with the SQL Server SP3 service pack then is it possible for you to upgrade to SP4 service pack.
What is the database status ? IS it in suspect mode ?
According to you the error message you receive is :
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x000000001b4000 in file 'C:\Inetpub\wwwroot\SCReviewV3\DB\SCReviewV3_Data.MDF'.
Connection Broken
Generally 823 error message occurs when:
• A ReadFile, WriteFile, ReadFileScatter, WriteFileGather, or etOverlappedResult operation results in any operating system error code.
• The page number on the page read from disk is not the expected page ID.
• The data transfer size is not valid.
• A torn read is detected when torn page detection is enabled.
• A stale read is detected when stale read detection is enabled.
What is the bad page ID: This message means that the pageID on the page header is not the expected page that was read from the disk. For example, if SQL Server 2000 provides a file offset for database file 1 that is for logical page 100, the pageID on the page header for that 8 KB page should be 1:100. If not, the bad page ID is included in the logical I/O check failure message.
SQL Server 2000 uses standard Windows API calls, such as ReadFile, ReadFileScatter, WriteFile, and WriteFileGather, to perform I/O with its database files. When SQL Server 2000 uses the Windows API calls, the file has already been opened successfully, or SQL Server 2000 would not try to read from it or write to it. Therefore, if a Windows API call is not successful and if the error is anything other than the operating system error 6 ("Invalid Handle"), the error is likely being raised in Windows or by a lower-level software component, such as a device driver. Because the operating system error 6 is an invalid handle, the problem may occur if SQL Server is using an invalid handle to make a Windows API call. However, this may still be a system problem.
Because SQL Server has already successfully opened the file and did not receive an “Invalid Handle” error, the error is likely being raised in a lower-level kernel software component, such as the file system or a device driver. This problem does not indicate a problem in SQL Server, and it must be investigated as an issue with the file system or a device driver that is associated with the file.
Steps you should take to troubleshoot is that :
1. Please check your hardware with your hardware vendore.
2. Run SQLIOSTRESS utility. You can use this utility to perform stress tests on disk subsystems to simulate Microsoft SQL Server 2000 and Microsoft SQL Server 7.0 read, write, checkpoint, backup, sort, and read ahead activities.
3. Enable the trace flag 818 as SQL startup parameter.
Trace flag 818 enables an in-memory ring buffer that is used for tracking the last 2,048 successful write operations that are performed by the computer running SQL Server, not including sort and workfile I/Os. When errors such as Error 605, 823, or 3448 occur, the incoming buffer's log sequence number (LSN) value is compared to the recent write list. If the LSN that is retrieved during the read operation is older than the one specified during the write operation, a new error message is logged in the SQL Server error log. Most SQL Server write operations occur as checkpoints or as lazy writes. A lazy write is a background task that uses asynchronous I/O. The implementation of the ring buffer is lightweight, thereby making the performance affect on the system negligible.
4. If the database is in the suspect mode then try to put the database to the emerygency mode and run DBCC DBRECOVER(database name) command. AS i this case the MDF file is corrupted and I am not sure the above command will help you but if there is any allocation problem that will be fixed by this command and the once the database is UP we can run DBCC CHECKDB.
5. If the database is not in the emergency mode please script out the database script using ALL TASK --> SCRIPT.
I still recomend you to check the hardware of the system and update to latest firmaware. I have seen in many cases that the hardware vendore will report that there is no problem with the hardware. In that case plaese run the SQLIOSTRESS and check the output which reports for stale read / lost write.
Lost Write: A successful call to the WriteFile API, but the operating system, a driver, or the caching controller does not correctly flush the data to the physical media even though SQL Server is informed that the write was successful.
Stale Read: A successful call to the ReadFile API, but the operating system, a driver, or the caching controller incorrectly returns an older version of the data. Microsoft has confirmed scenarios where a WriteFile API call returns as successful, but an immediate, successful read of the same data block returns older data, including data that is likely stored in a hardware read cache. Sometimes, this problem occurs because of a read cache problem. In other cases, the write data is never actually written to the physical disk.
Hope this will help you.
Links for your reference:
http://support.microsoft.com/kb/307775/en-us
http://support.microsoft.com/kb/231619
http://support.microsoft.com/kb/828339/en-us
http://support.microsoft.com/kb/826433/en-us
Regards,
Minaz Amin
email id : meenakshikundar@yahoo.com
Phno: 9341735900.
"More Green More Oxygen !! Plant a tree today"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply