MSSQL TIPS

Contents

  1. MSSQL TIPS
    1. Windows MSSQL Mirroring how to
    2. Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error
      1. On primary SQL server
      2. On mirror SQL server
      3. Check endpoint status
      4. How to solve this
    3. user/role are already exist
    4. How to Backup SQL Databases to a Network Share
    5. Script to check that backup files still exist for SQL Server
    6. MSSQL REPAIR DATA LOSS
      1. Solution For this Error
    7. how to check if the log-in exists in sql server
    8. SQL Server detected a logical consistency-based I/O error
    9. 1813 error repair
    10. Refenrences

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

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

   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. Check the integrity of the file system with the chkdsk command. =>c:\chkdsk

  2. Follow the Steps to Recover the Tables.

1. Check which Table is Corrupted using

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

4. Run this Query to Recover the Table/DB.

(OR)

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

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

   1 dbcc
   2 checktable('my_TABLE',repair_allow_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

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