Differences between revisions 5 and 6
⇤ ← Revision 5 as of 2015-10-12 04:49:53 →
Size: 4713
Editor: localhost
Comment:
← Revision 6 as of 2015-10-12 04:50:31 → ⇥
Size: 4703
Editor: localhost
Comment:
Deletions are marked like this. Additions are marked like this.
Line 51: Line 51:
 ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Line 55: Line 55:
 DBCC CheckTable ('corrupted_table', REPAIR_ALLOW_DATA_LOSS) DBCC CheckTable ('corrupted_table', REPAIR_ALLOW_DATA_LOSS)
Line 57: Line 57:
 (OR) (OR)
Line 59: Line 59:
 DBCC CheckDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS) DBCC CheckDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
Line 69: Line 69:
 è Select the DB è Select the DB
Line 71: Line 71:
 è Right Click the DB è Right Click the DB
Line 73: Line 73:
 è Goto Properties è Goto Properties
Line 75: Line 75:
 è Select Options è Select Options
Line 77: Line 77:
 è Under Other Option select the Page Verify è Under Other Option select the Page Verify
Line 79: Line 79:
 è Select the Option CHECKSUM. è Select the Option CHECKSUM.

MSSQL TIPS

Contents

  1. MSSQL TIPS
    1. MSSQL REPAIR DATA LOSS
      1. Solution For this Error

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

  1. Torn Page Error -> it shows your Hardware Problems. Check your Hardware issues First.

  2. 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

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')

désert/Windows/MSSQL (last edited 2016-11-08 14:58:51 by localhost)