January 9, 2012 at 11:26 pm
Hi,
Two months back Installed SQL server 2008 enterprise edition in PC based server.. Now database running with consistency error.. Could anyone suggestion me, how to resolve this error?
DBCC Checkdb 'dbname' with data_purity
Result
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 0, partition ID 72057594040745984, alloc unit ID 72057594041794560 (type In-row data): Page (1:425) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 485576768, index ID 0, partition ID 72057594040745984, alloc unit ID 72057594041794560 (type In-row data), page (1:425). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
SQL Error Log and system Event Viewer
Message
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xbfc9c6fa; actual: 0x9fc9c6fa). It occurred during a read of page (1:425) in database ID 5 at offset 0x00000000352000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HWDATA7.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Thanks
ananda
January 9, 2012 at 11:44 pm
Data file can be repaired by DBCC repair command but there is risk of data loss so first take the database backup and also record all table count in excel file for compare the data after repair.
Step.1 Backup database.
Step.2 Get all tables record count.
Step.3 SET database in single user mode.
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Step.4 RUN DBCC command-
DBCC CHECKDB(DB_NAME,REPAIR_ALLOW_DATA_LOSS)
Step.5 Get again all tables record count and compare with old record count.
SQL Query to get table record count-
DECLARE @T_Name VARCHAR(250)
DECLARE @COUNT INT
DECLARE @SQL VARCHAR(2000)
CREATE TABLE #T_Info(ID INT IDENTITY(1,1),T_Name VARCHAR(200),D_Count INT)
DECLARE TINFO_CUR CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
OPEN TINFO_CUR
FETCH NEXT FROM TINFO_CUR INTO @T_Name
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL='INSERT INTO #T_Info(T_Name,D_Count) SELECT '''+@T_Name+''',COUNT(*) FROM '+@T_Name+''
EXECUTE (@SQL)
FETCH NEXT FROM TINFO_CUR INTO @T_Name
END
CLOSE TINFO_CUR
DEALLOCATE TINFO_CUR
SELECT * FROM #T_Info ORDER BY T_NAME
DROP TABLE #T_Info
January 10, 2012 at 12:04 am
I would first look for things that may cause your kind of corruption:
A good starter is : http://sqlskills.com/BLOGS/PAUL/post/Example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-IO-errors.aspx
You'll find more corruption/checksum related blogs on that site.
http://www.sqlservercentral.com/articles/Corruption/65804/ also contains very helpful information on your quest to solve your issue.
Only after having checked the options, I would try a "repair with data loss".
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 10, 2012 at 12:04 am
hi, Thanks for your reply and steps for sorted out. there is only one record was loss.
I am using DBCC CHECKTABLE ('Table_name', REPAIR_ALLOW_DATA_LOSS)
thanks
ananda
January 10, 2012 at 3:01 am
For future reference, repair should be the absolute last resort for corruption, no a knee-jerk first thing done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2012 at 3:21 am
GilaMonster (1/10/2012)
For future reference, repair should be the absolute last resort for corruption, no a knee-jerk first thing done.
Hi Gail, what could be alternative solution to aviod data loss? when facing this type of error occurred in database.
I am facing first time this type of IO Error in CHECKDB. Eeven though not able to count all the records that particular table for due to 823 - a hard IO error.. So I am decided to ran DBCC CHECKTABLE ('Tablename', REPAIR_ALLOW_DATA_LOSS) command. after ran that those error was resolved.
thanks
ananda
January 10, 2012 at 4:34 am
CheckDB or CheckTable with repair allow data loss will lose data, that's why it's named that way (there are a couple cases where it won't, but they're rarer cases)
For recovery without data loss, take a tail-log backups and restore from backup, either full database, filegroup, file or page and then roll all the logs forward.
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2014 at 3:00 am
Thanx work perfect for me.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply