Differences between revisions 1 and 12 (spanning 11 versions)

MSSQL TIPS

Contents

MSSQL TIPS

MSSQL REPAIR DATA LOSS

   1 2015-10-10 12:50:28.08 spid52      SQL Server detected a logical consistency-based I/O error: 页撕裂(签名应该为: 0x55555555，但实际为: 0xaaaa5555). It occurred during a 读取 of page (1:11887) in database ID 18 at offset 0x00000005cde000 in file 'D:\Program Files\imcdata\ead_data01.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.
   2 2015-10-10 12:50:29.18 登录          错误: 18456，严重性: 14，状态: 38。
   3 2015-10-10 12:50:29.18 登录          Login failed for user 'imc_syslog'. 原因: 无法打开明确指定的数据库。 [客户端: 127.0.0.1]
   4 2015-10-10 12:50:31.68 spid70      Error: 824, Severity: 24, State: 2.
   5 2015-10-10 12:50:31.68 spid70      SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x0000aaaa). It occurred during a read of page (4:990) in database ID 5 at offset 0x000000007bc000 in file 'D:\Program Files\imcdata\config_db03.ndf'.  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.
   6 2015-10-10 12:50:31.96 spid80      Error: 824, Severity: 24, State: 2.
   7 
   8 
   9 2015-10-10 12:52:37.17 spid56      SQL Server detected a logical consistency-based I/O error: 页撕裂(签名应该为: 0x55555555，但实际为: 0xaaaa5555). It occurred during a 读取 of page (1:11887) in database ID 18 at offset 0x00000005cde000 in file 'D:\Program Files\imcdata\ead_data01.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.
  10 2015-10-10 12:52:44.19 登录          错误: 18456，严重性: 14，状态: 38。
  11 2015-10-10 12:52:44.19 登录          Login failed for user 'imc_syslog'. 原因: 无法打开明确指定的数据库。 [客户端: 127.0.0.1]
  12 2015-10-10 12:52:45.72 spid61      Error: 824, Severity: 24, State: 2.
  13 2015-10-10 12:52:45.72 spid61      SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x5555aaaa). It occurred during a read of page (4:766) in database ID 5 at offset 0x000000005fc000 in file 'D:\Program Files\imcdata\config_db03.ndf'.  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.
  14 2015-10-10 12:52:45.77 spid65      Error: 824, Severity: 24, State: 2.

Solution For this Error

Torn Page Error -> it shows your Hardware Problems. Check your Hardware issues First. Check the integrity of the file system with the chkdsk command. =>c:\chkdsk
Follow the Steps to Recover the Tables.

1. Check which Table is Corrupted using

DBCC Checktable ('Your_Table_Name’).

2. Goto SQL Server Managemant Studio

è Select the DB

è Right Click the DB

è Goto Properties

è Select Options

è Under Other Option select the Page Verify

è Select as NONE.

3. Run this Query to Change DB into Single User Mode

ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Run this Query to Recover the Table/DB.

DBCC CheckTable ('corrupted_table', REPAIR_ALLOW_DATA_LOSS)

(OR)

DBCC CheckDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

5. Once this Execution Completed Sql returns “Errors are Corrected”

6. Run this Query to Change the DB into MultiUser Mode

ALTER DATABASE ApplicationManager SET MULTI_USER

7. Goto SQL Server Managemant Studio

è Select the DB

è Right Click the DB

è Goto Properties

è Select Options

è Under Other Option select the Page Verify

è Select the Option CHECKSUM.

8. Now Run DBCC CHECKDB('Your DB')

SQL Server detected a logical consistency-based I/O error

we have just executed the below code to repair the page ..

   1 dbcc
   2 checktable('my_TABLE',repair_allow_data_loss)

Now its working fine.. Its my stage environment we can tolerate the few data loss...

1813 error repair

http://www.fryan0911.com/2006/07/sql-server-problem-cannot-attach-sql.html

Refenrences

http://dba.stackexchange.com/questions/36505/sql-server-2008-cant-repair-consistency

http://sureshbabum.blogspot.com/2011/03/sql-dba-corrupted-db-logical.html

http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ba0e823e-7c12-4085-b350-fde760b23d12/sql-server-detected-a-logical-consistencybased-io-error?forum=sqldatabaseengine

https://support.microsoft.com/en-us/kb/2015748

-  ⇤ ← Revision 1 as of 2015-10-12 04:46:30 → 
  Size: 4705
  Editor: localhost
  Comment:
+   ← Revision 12 as of 2015-10-12 05:13:17 → ⇥
  Size: 5726
  Editor: localhost
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 3:
+<<TableOfContents()>>
-Line 4:
+Line 5:
-{{{!#highlight python
+{{{#!highlight python
-Line 26:
+Line 27:
- * Torn Page Error -> it shows your Hardware Problems. Check your Hardware issues First.
+ I. Torn Page Error -> it shows your Hardware Problems. Check your Hardware issues First. Check the integrity of the file system with the ''chkdsk'' command. =>''c:\chkdsk''
-Line 28:
+Line 29:
- * Follow the Steps to Recover the Tables.
+ I. Follow the Steps to Recover the Tables.
-Line 30:
+Line 31:
 . Check which Table is Corrupted using
-Line 34:
+Line 35:
 . Goto SQL Server Managemant Studio
-Line 36:
+Line 37:
- è Select the DB
+è Select the DB
-Line 38:
+Line 39:
- è Right Click the DB
+è Right Click the DB
-Line 40:
+Line 41:
- è Goto Properties
+è Goto Properties
-Line 42:
+Line 43:
- è Select Options
+è Select Options
-Line 44:
+Line 45:
- è Under Other Option select the Page Verify
+è Under Other Option select the Page Verify
-Line 46:
+Line 47:
- è Select as NONE.
+è Select as NONE.
-Line 48:
+Line 49:
 . Run this Query to Change DB into Single User Mode
-Line 52:
+Line 53:
 . Run this Query to Recover the Table/DB.
-Line 56:
+Line 57:
- (OR)
+(OR)
-Line 60:
+Line 61:
 . Once this Execution Completed Sql returns “Errors are Corrected”
-Line 62:
+Line 63:
 . Run this Query to Change the DB into MultiUser Mode
-Line 66:
+Line 67:
-. 7. Goto SQL Server Managemant Studio
+. Goto SQL Server Managemant Studio
-Line 68:
+Line 69:
- è Select the DB
+è Select the DB
-Line 70:
+Line 71:
- è Right Click the DB
+è Right Click the DB
-Line 72:
+Line 73:
- è Goto Properties
+è Goto Properties
-Line 74:
+Line 75:
- è Select Options
+è Select Options
-Line 76:
+Line 77:
- è Under Other Option select the Page Verify
+è Under Other Option select the Page Verify
-Line 78:
+Line 79:
- è Select the Option CHECKSUM.
+è Select the Option CHECKSUM.
-Line 80:
+Line 81:
-. Now Run DBCC CHECKDB('Your DB')
+. Now Run DBCC CHECKDB('Your DB')

==  SQL Server detected a logical consistency-based I/O error  ==

 *we have just executed the below code to repair the page ..

{{{#!highlight python
dbcc
checktable('my_TABLE',repair_allow_data_loss)
}}}

 Now its working fine..  Its my stage environment we can tolerate the few data loss... :) 

== 1813 error repair ==
http://www.fryan0911.com/2006/07/sql-server-problem-cannot-attach-sql.html

== Refenrences ==
http://dba.stackexchange.com/questions/36505/sql-server-2008-cant-repair-consistency

http://sureshbabum.blogspot.com/2011/03/sql-dba-corrupted-db-logical.html

http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ba0e823e-7c12-4085-b350-fde760b23d12/sql-server-detected-a-logical-consistencybased-io-error?forum=sqldatabaseengine

https://support.microsoft.com/en-us/kb/2015748

Diff for "désert/Windows/MSSQL"