AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • Início
  • system&network
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • Início
  • system&network
    • Recentes
    • Highest score
    • tags
  • Ubuntu
    • Recentes
    • Highest score
    • tags
  • Unix
    • Recentes
    • tags
  • DBA
    • Recentes
    • tags
  • Computer
    • Recentes
    • tags
  • Coding
    • Recentes
    • tags
Início / dba / Perguntas / 117719
Accepted
MySQL DBA
MySQL DBA
Asked: 2015-10-13 02:15:11 +0800 CST2015-10-13 02:15:11 +0800 CST 2015-10-13 02:15:11 +0800 CST

SQL Server 2012 Always On Setup com dois nós

  • 772

Eu AlwaysOnconfigurei com dois nós de dados e uma testemunha. Enfrentei um problema em que meu servidor PRIMARY reiniciou abruptamente.

Aqui estão alguns problemas que enfrentei durante esse período.

  1. Devido ao reinício abrupto da PRIMARY Replica, o banco de dados entrou no modo RECOVERY.

  2. O banco de dados levou cerca de 1 hora para se recuperar.

  3. Durante esta Fase de RECUPERAÇÃO da Réplica PRIMÁRIA. O Servidor Secundário (que agora é PRIMÁRIO devido ao FAILOVER) estava enfrentando tempos limite e estava observando lentidão.

Olhando para os logs, pude ver logs sobre roll forward e rollback que aconteceram no banco de dados. Mas, em primeiro lugar, estou me perguntando qual poderia ser a razão pela qual minha recuperação do DB demorou mais?

Além disso, gostaria de obter uma entrada se eu adicionar mais um nó secundário a esta configuração, isso me ajudará de forma eficiente?

Adicionando o log de erros:

2015-10-12 16:20:26.30 spid31s     The recovery LSN (6821:15912:1) was identified for the database with ID 12. This is an informational message only. No user action is required.
2015-10-12 16:23:56.81 spid44s     Recovery of database 'A' (11) is 0% complete (approximately 1168 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-10-12 16:23:57.28 spid44s     Recovery of database 'A' (11) is 0% complete (approximately 1091 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-10-12 16:23:57.28 spid44s     Recovery of database 'A' (11) is 0% complete (approximately 891 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 16:24:17.32 spid44s     Recovery of database 'A' (11) is 31% complete (approximately 44 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 16:24:50.53 spid6s      SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [Z:\SQLData\A\A.mdf] in database [A] (11).  The OS file handle is 0x0000000000000B9.  The offset of the latest long I/O is: 0x0000041135800
2015-10-12 16:24:55.46 spid44s     Recovery of database 'A' (11) is 53% complete (approximately 51 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 16:25:15.50 spid44s     Recovery of database 'A' (11) is 85% complete (approximately 13 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 16:25:49.69 spid44s     3033 transactions rolled forward in database 'A' (11:0). This is an informational message only. No user action is required.
2015-10-12 16:25:50.15 spid44s     Recovery completed for database A (database ID 11) in 290 second(s) (analysis 480 ms, redo 87801 ms, undo 0 ms.) This is an informational message only. No user action is required.
2015-10-12 16:25:52.35 spid44s     CHECKDB for database 'A' finished without errors on 2012-10-27 22:19:16.470 (local time). This is an informational message only; no user action is required.
2015-10-12 16:42:57.67 spid24s     AlwaysOn Availability Groups connection with primary database established for secondary database 'A' on the availability replica with Replica ID: {}. This is an informational message only. No user action is required.
2015-10-12 16:42:57.67 spid24s     The recovery LSN (216726:2384:1) was identified for the database with ID 11. This is an informational message only. No user action is required.
2015-10-12 16:42:57.91 spid24s     AlwaysOn Availability Groups connection with primary database established for secondary database 'A' on the availability replica with Replica ID: {}. This is an informational message only. No user action is required.
2015-10-12 16:42:57.91 spid24s     The recovery LSN (216726:2384:1) was identified for the database with ID 11. This is an informational message only. No user action is required.
2015-10-12 16:42:58.53 spid24s     Error: 35278, Severity: 17, State: 1.
2015-10-12 16:42:58.53 spid24s     Availability database 'A', which is in the secondary role, is being restarted to resynchronize with the current primary database. This is an informational message only. No user action is required.
2015-10-12 16:42:58.53 spid29s     Nonqualified transactions are being rolled back in database A for an AlwaysOn Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.
2015-10-12 16:42:58.53 spid38s     AlwaysOn Availability Groups connection with primary database terminated for secondary database 'A' on the availability replica with Replica ID: {}. This is an informational message only. No user action is required.
2015-10-12 16:42:58.79 spid29s     Starting up database 'A'.
2015-10-12 16:49:45.32 spid29s     Recovery of database 'A' (11) is 0% complete (approximately 1168 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-10-12 16:49:45.78 spid29s     Recovery of database 'A' (11) is 0% complete (approximately 1091 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-10-12 16:49:45.78 spid29s     Recovery of database 'A' (11) is 0% complete (approximately 891 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 16:50:05.80 spid29s     Recovery of database 'A' (11) is 35% complete (approximately 37 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 16:50:25.82 spid29s     Recovery of database 'A' (11) is 68% complete (approximately 18 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 16:50:44.66 spid29s     AlwaysOn Availability Groups connection with primary database established for secondary database 'A' on the availability replica with Replica ID: {}. This is an informational message only. No user action is required.
2015-10-12 16:50:44.66 spid29s     The recovery LSN (216726:2386:1) was identified for the database with ID 11. This is an informational message only. No user action is required.
2015-10-12 16:50:44.66 spid29s     Error: 35286, Severity: 16, State: 1.
2015-10-12 16:50:44.66 spid29s     Using the recovery LSN (216726:2384:1) stored in the metadata for the database with ID 11. This is an informational message only. No user action is required.
2015-10-12 16:53:12.38 spid29s     Error: 35278, Severity: 17, State: 1.
2015-10-12 16:53:12.38 spid29s     Availability database 'A', which is in the secondary role, is being restarted to resynchronize with the current primary database. This is an informational message only. No user action is required.
2015-10-12 16:53:12.38 spid29s     Nonqualified transactions are being rolled back in database A for an AlwaysOn Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.
2015-10-12 16:53:12.40 spid31s     AlwaysOn Availability Groups connection with primary database terminated for secondary database 'A' on the availability replica with Replica ID: {}. This is an informational message only. No user action is required.
2015-10-12 16:53:14.45 spid29s     Starting up database 'A'.
2015-10-12 17:05:44.30 spid29s     Recovery of database 'A' (11) is 0% complete (approximately 1168 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-10-12 17:05:44.76 spid29s     Recovery of database 'A' (11) is 0% complete (approximately 1091 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-10-12 17:05:44.76 spid29s     Recovery of database 'A' (11) is 0% complete (approximately 891 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 17:06:04.88 spid29s     Recovery of database 'A' (11) is 31% complete (approximately 45 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 17:06:24.92 spid29s     Recovery of database 'A' (11) is 65% complete (approximately 21 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-10-12 17:06:45.55 spid29s     AlwaysOn Availability Groups connection with primary database established for secondary database 'A' on the availability replica with Replica ID: {}. This is an informational message only. No user action is required.
2015-10-12 17:06:45.55 spid29s     The recovery LSN (216726:19027:80) was identified for the database with ID 11. This is an informational message only. No user action is required.
2015-10-12 17:06:48.97 spid29s     3034 transactions rolled forward in database 'A' (11:0). This is an informational message only. No user action is required.
2015-10-12 17:06:49.04 spid29s     Recovery completed for database A (database ID 11) in 710 second(s) (analysis 470 ms, redo 60412 ms, undo 0 ms.) This is an informational message only. No user action is required.
2015-10-12 17:06:49.07 spid20s     AlwaysOn Availability Groups connection with primary database established for secondary database 'A' on the availability replica with Replica ID: {}. This is an informational message only. No user action is required.
2015-10-12 17:06:49.08 spid20s     The recovery LSN (216726:19027:80) was identified for the database with ID 11. This is an informational message only. No user action is required.
sql-server sql-server-2012
  • 1 1 respostas
  • 2003 Views

1 respostas

  • Voted
  1. Best Answer
    CraggersSQL
    2015-10-15T00:35:47+08:002015-10-15T00:35:47+08:00

    O problema possivelmente está no erro que você está vendo: Erro: 35278. Quando isso acontecer, você provavelmente observará que o banco de dados fica em um estado de reversão por um longo período de tempo.

    Isso pode ser causado por vários motivos, normalmente uma transação de execução longa.

    Os tempos limite que você estava enfrentando podem ter sido causados ​​pelo tráfego enviado entre as réplicas para o banco de dados ser recuperado e voltar, no entanto, você tem certeza de que os tempos limite não foram causados ​​por algum outro problema?

    Gostaria de saber qual é a sua estratégia de backup neste banco de dados e quando o último backup completo foi executado antes deste failover. Recentemente, experimentei isso em um ambiente de teste em que os backups não estavam sendo executados. Um backup completo e um backup de log subsequente permitiram que um failover ocorresse sem problemas, o erro não estava presente e o tempo de recuperação foi muito rápido.

    • 2

relate perguntas

  • SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

  • Preciso de índices separados para cada tipo de consulta ou um índice de várias colunas funcionará?

  • Quando devo usar uma restrição exclusiva em vez de um índice exclusivo?

  • Quais são as principais causas de deadlocks e podem ser evitadas?

  • Como determinar se um Índice é necessário ou necessário

Sidebar

Stats

  • Perguntas 205573
  • respostas 270741
  • best respostas 135370
  • utilizador 68524
  • Highest score
  • respostas
  • Marko Smith

    conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

    • 12 respostas
  • Marko Smith

    Como fazer a saída do sqlplus aparecer em uma linha?

    • 3 respostas
  • Marko Smith

    Selecione qual tem data máxima ou data mais recente

    • 3 respostas
  • Marko Smith

    Como faço para listar todos os esquemas no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Listar todas as colunas de uma tabela especificada

    • 5 respostas
  • Marko Smith

    Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

    • 4 respostas
  • Marko Smith

    Como você mysqldump tabela (s) específica (s)?

    • 4 respostas
  • Marko Smith

    Listar os privilégios do banco de dados usando o psql

    • 10 respostas
  • Marko Smith

    Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

    • 4 respostas
  • Marko Smith

    Como faço para listar todos os bancos de dados e tabelas usando o psql?

    • 7 respostas
  • Martin Hope
    Jin conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane Como faço para listar todos os esquemas no PostgreSQL? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh Por que o log de transações continua crescendo ou fica sem espaço? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland Listar todas as colunas de uma tabela especificada 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney O MySQL pode realizar consultas razoavelmente em bilhões de linhas? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx Como posso monitorar o andamento de uma importação de um arquivo .sql grande? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison Como você mysqldump tabela (s) específica (s)? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas Como posso cronometrar consultas SQL usando psql? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas Como faço para listar todos os bancos de dados e tabelas usando o psql? 2011-02-18 00:45:49 +0800 CST

Hot tag

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • Início
  • Perguntas
    • Recentes
    • Highest score
  • tag
  • help

Footer

AskOverflow.Dev

About Us

  • About Us
  • Contact Us

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve