Differences between revisions 11 and 12

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 11 as of 2015-10-12 05:12:07 → 
  Size: 5718
  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 27:
- 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
+ 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''

Diff for "désert/Windows/MSSQL"