#acl merlyn:read,write All:read = MSSQL TIPS = <> == Windows MSSQL Mirroring how to == http://www.sqlservercentral.com/Forums/Topic1146133-149-1.aspx == Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error == https://social.technet.microsoft.com/Forums/fr-FR/73fb15c0-9270-4cbf-a74e-544639e792da/error-1418-fails-to-connect-mirror-server?forum=sqldatabasemirroring === On primary SQL server === {{{ 03/19/2016 19:18:17,spid33s,未知,Database mirroring connection error 4 'An error occurred while receiving data: '10054(远程主机强迫关闭了一个现有的连接。)'.' for 'TCP://mirror.duowan.com:5022'. }}} === On mirror SQL server === {{{ 03/19/2016 19:18:33,Logon,未知,Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 172.26.10.10] }}} === Check endpoint status === * SELECT state_desc FROM sys.database_mirroring_endpoints To start an endpoint, use the following Transact-SQL statement. {{{ ALTER ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR database_mirroring (ROLE = ALL); GO }}} Check that the ROLE is correct. On each server instance use the following Transact-SQL statement. {{{ SELECT role FROM sys.database_mirroring_endpoints; GO }}} To determine who has CONNECT permission for an endpoint. {{{ SELECT 'Metadata Check'; SELECT EP.name, SP.STATE, CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE FROM sys.server_permissions SP , sys.endpoints EP WHERE SP.major_id = EP.endpoint_id ORDER BY Permission,grantor, grantee; GO }}} === How to solve this === {{{ 1- delete logins on both servers. DROP LOGIN HOST_A_test; 2- delete their certificates. DROP CERTIFICATE cert_name; 3- delete their users. 4- recreate logins. 5- recreate the users. 6- recreate the certificate 7- shoot the grant connect statement at both servers. }}} == user/role are already exist == sp_change_users_login 'update_one', 'AAA', 'AAA' == How to Backup SQL Databases to a Network Share == http://www.howtogeek.com/51788/how-to-backup-sql-databases-to-a-network-share/ == Script to check that backup files still exist for SQL Server == https://www.mssqltips.com/sqlservertip/1800/script-to-check-that-backup-files-still-exist-for-sql-server/ == 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 === 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. 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') == how to check if the log-in exists in sql server == select name from master..syslogins == 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