MSSQL TIPS
MSSQL REPAIR DATA LOSS
{{{!#highlight python 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. 2015-10-10 12:50:29.18 登录 错误: 18456，严重性: 14，状态: 38。 2015-10-10 12:50:29.18 登录 Login failed for user 'imc_syslog'. 原因: 无法打开明确指定的数据库。 [客户端: 127.0.0.1] 2015-10-10 12:50:31.68 spid70 Error: 824, Severity: 24, State: 2. 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. 2015-10-10 12:50:31.96 spid80 Error: 824, Severity: 24, State: 2.
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. 2015-10-10 12:52:44.19 登录 错误: 18456，严重性: 14，状态: 38。 2015-10-10 12:52:44.19 登录 Login failed for user 'imc_syslog'. 原因: 无法打开明确指定的数据库。 [客户端: 127.0.0.1] 2015-10-10 12:52:45.72 spid61 Error: 824, Severity: 24, State: 2. 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. 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.
- Check which Table is Corrupted using DBCC Checktable ('Your_Table_Name’).
- 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.
- Run this Query to Change DB into Single User Mode ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- 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)
- Once this Execution Completed Sql returns “Errors are Corrected”
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.
- Now Run DBCC CHECKDB('Your DB')