Differences between revisions 3 and 5 (spanning 2 versions)

MSSQL TIPS

Contents

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

Torn Page Error -> it shows your Hardware Problems. Check your Hardware issues First.
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')

-  ⇤ ← Revision 3 as of 2015-10-12 04:47:38 → 
  Size: 4728
  Editor: localhost
  Comment:
+   ← Revision 5 as of 2015-10-12 04:49:53 → ⇥
  Size: 4713
  Editor: localhost
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 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.
 Line 29:
- * Follow the Steps to Recover the Tables.
+ I. Follow the Steps to Recover the Tables.
 Line 31:
 . Check which Table is Corrupted using
 Line 35:
 . Goto SQL Server Managemant Studio
 Line 37:
- è Select the DB
+è Select the DB
 Line 39:
- è Right Click the DB
+è Right Click the DB
 Line 41:
- è Goto Properties
+è Goto Properties
 Line 43:
- è Select Options
+è Select Options
 Line 45:
- è Under Other Option select the Page Verify
+è Under Other Option select the Page Verify
 Line 47:
- è Select as NONE.
+è Select as NONE.
 Line 49:
 . Run this Query to Change DB into Single User Mode
 Line 53:
 . Run this Query to Recover the Table/DB.
 Line 61:
 . Once this Execution Completed Sql returns “Errors are Corrected”
 Line 63:
 . Run this Query to Change the DB into MultiUser Mode
 Line 67:
-. 7. Goto SQL Server Managemant Studio
+. Goto SQL Server Managemant Studio
 Line 81:
 . Now Run DBCC CHECKDB('Your DB')

Diff for "désert/Windows/MSSQL"

MSSQL TIPS

MSSQL REPAIR DATA LOSS

Solution For this Error