All perguntas(dba)

Uma das minhas unidades de arquivo de log do banco de dados de produção foi preenchida e notei as mensagens abaixo no log de erros do SQL Server.

2022-02-13 03:16:24.500 spid72  Error: 9002, Severity: 17, State: 4.
2022-02-13 03:16:24.500 spid72  The transaction log for database 'huge_database' is full due to 'ACTIVE_TRANSACTION'.
2022-02-13 03:16:26.060 spid72  Error: 9002, Severity: 17, State: 4.
2022-02-13 03:16:26.060 spid72  The transaction log for database 'huge_database' is full due to 'ACTIVE_TRANSACTION'.
2022-02-13 03:16:26.070 spid72  Error: 3314, Severity: 21, State: 3.
2022-02-13 03:16:26.070 spid72  During undoing of a logged operation in database 'huge_database', an error occurred at log record ID (2766:550754:254). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2022-02-13 03:16:26.090 spid72  Database huge_database was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
2022-02-13 03:16:26.090 spid72  Error: 3314, Severity: 21, State: 5.
2022-02-13 03:16:26.090 spid72  During undoing of a logged operation in database 'huge_database', an error occurred at log record ID (2678:51796:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2022-02-13 03:16:32.900 spid48s Starting up database 'huge_database'.
2022-02-13 03:16:37.980 spid48s Recovery of database 'huge_database' (20) is 0% complete (approximately 89573 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2022-02-13 03:16:57.980 spid48s Recovery of database 'huge_database' (20) is 0% complete (approximately 60835 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
...
2022-02-13 04:26:15.160 spid36s Recovery of database 'huge_database' (20) is 85% complete (approximately 721 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
...
2022-02-13 04:36:35.230 spid36s Recovery of database 'huge_database' (20) is 99% complete (approximately 1 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2022-02-13 04:36:35.790 spid36s 1 transactions rolled back in database 'huge_database' (20:0). This is an informational message only. No user action is required.
2022-02-13 04:36:35.790 spid36s Recovery is writing a checkpoint in database 'huge_database' (20). This is an informational message only. No user action is required.
2022-02-13 04:36:36.850 spid36s Recovery completed for database huge_database (database ID 20) in 4804 second(s) (analysis 4881 ms, redo 1196931 ms, undo 3600721 ms.) This is an informational message only. No user action is required.  

Parece que a unidade de log do banco de dados está cheia e iniciou um processo de recuperação, que levou mais de uma hora. Por que ele precisa se recuperar neste caso? Eu quero reproduzi-lo, mas não consegui. Abaixo está o meu código:

CREATE DATABASE MyDatabase
ON 
    (NAME = MyDatabase_Data,
    FILENAME = 'f:\mssql\data\MyDatabase_Data.mdf',
    SIZE = 10MB,
    MAXSIZE = 1000MB,
    FILEGROWTH = 5MB)
LOG ON
    (NAME = MyDatabase_Log,
    FILENAME = 'U:\data\MyDatabase_Log.ldf',
    SIZE = 5MB,
    MAXSIZE = 500MB,
    FILEGROWTH = 1MB);
GO

USE MyDatabase;
GO

CREATE TABLE MyTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Data VARCHAR(MAX) NOT NULL
);
GO

BEGIN TRANSACTION;

DECLARE @i INT = 0;
WHILE @i < 1000000
BEGIN
    INSERT INTO MyTable (Data) VALUES (REPLICATE('A', 8000));
    SET @i = @i + 1;
END;

-- Don't commit the transaction
-- COMMIT TRANSACTION;

Quando executo meu código, recebo isso no SSMS:

Msg 9002, Nível 17, Estado 4, Linha 30 O log de transação do banco de dados 'MyDatabase' está cheio devido a 'ACTIVE_TRANSACTION' e o lsn de assalto é (39:24:1).

E isso do log de erros do SQL Server:

2023-08-18 02:59:54.200 spid84  Error: 17053, Severity: 16, State: 1.
2023-08-18 02:59:54.200 spid84  U:\data\MyDatabase_Log.ldf: Operating system error 112(There is not enough space on the disk.) encountered.
2023-08-18 02:59:55.200 spid84  Error: 9002, Severity: 17, State: 4.
2023-08-18 02:59:55.200 spid84  The transaction log for database 'MyDatabase' is full due to 'ACTIVE_TRANSACTION' and the holdup lsn is (39:24:1).  

Não há processo de recuperação. Por que? Como imitar meu problema de banco de dados de produção?

Atualizar:

Aliás, durante a recuperação do meu banco de dados de produção, ainda consigo acessá-lo. Ele ainda tem o mesmo status que em outros bancos de dados em sys.databases. Isso é esperado? Embora os bancos de dados em recuperação não estejam acessíveis.

Atualização2:

Ao desfazer uma operação registrada no banco de dados 'huge_database', ocorreu um erro no ID do registro de log (2678:51796:1).

Este log de erro parece que o SQL não reservou espaço suficiente para reverter. Lembrei que Paul Randal disse que o SQL Server sempre reserva algum log para rollback. Se sim, por que isso pode acontecer?

Meu aplicativo está centrado em "espaços de trabalho" independentes. Por muitas razões realmente boas (tudo, desde gerenciamento até segurança), sempre tivemos uma arquitetura de um banco de dados por espaço de trabalho. Cada banco de dados possui esquema idêntico, procedimentos armazenados, gatilhos, etc. Existe um “banco de dados de bancos de dados” que coordena tudo isso. Funciona bem.

O problema: escalabilidade. Recentemente, foi proposto que um cliente desejasse ter 100.000 espaços de trabalho. Obviamente, isso não é inicial para uma instância SQL. Além disso, cada área de trabalho pode ser bastante pequena, mas também pode haver uma distribuição de tamanho muito ampla - a maior área de trabalho pode ter 100 vezes o tamanho da mediana . O 1% superior dos espaços de trabalho poderia facilmente constituir mais de 90% das linhas em todos os espaços de trabalho.

Estou procurando opções para rearquitetar coisas para dar suporte a esse cenário, e aqui estão algumas coisas que considerei e os problemas que vejo em cada uma.

• Mantenha a arquitetura de vários bancos de dados, mas espalhe-a por várias instâncias SQL. O problema é o custo (administrativo e de infraestrutura). Se mantivermos um limite de 1.000 bancos de dados em cada instância, ainda serão 100 instâncias, espalhadas por quem sabe quantas VMs reais. Mas, como muitos dos espaços de trabalho serão pequenos (muito menores do que nossa média atual), a receita não aumentará de acordo. Portanto, acho que isso provavelmente está fora de questão e estou me concentrando agora em arquiteturas de banco de dados único.

• Cada espaço de trabalho compartilha as mesmas tabelas, indexadas pelo ID do espaço de trabalho. Portanto, toda tabela precisaria de uma nova coluna de ID de espaço de trabalho e toda consulta precisa adicionar a condição do espaço de trabalho na cláusula WHERE (ou mais provavelmente toda tabela real é agrupada em uma função de valor de tabela embutida que recebe o WorkspaceID; de qualquer maneira ...) O a chave primária de cada tabela também teria que ser redefinida para incluir o ID do espaço de trabalho, uma vez que nem todo PK agora é globalmente exclusivo. Em termos de programação, tudo bem, mas mesmo com indexação adequada e design de consulta perfeito (e não, nem todas as nossas consultas são perfeitas - a temida varredura de linha ainda acontece ocasionalmente) existe alguma maneira concebível de funcionar bem - para todos - como bancos de dados separados? Mais especificamente, podemos garantir que pequenos projetos não sofrerão com a presença de grandes projetos que podem ocupar 100x mais linhas do que os pequenos? E quais etapas específicas precisariam ser executadas, seja o tipo de índice a ser usado ou como escrever consultas para garantir que o otimizador sempre restrinja as coisas por ID do espaço de trabalho antes de fazer literalmente qualquer outra coisa?

• Particionamento - pelo que li, isso não ajuda no desempenho da consulta e parece que a MS recomenda limitar tabelas ou índices a 1.000 partições, portanto, isso também não ajudará.

• Crie o mesmo conjunto de tabelas, mas com um novo esquema para cada espaço de trabalho. Pensei nisso porque não há limites para o número de tabelas que um banco de dados pode ter além do limite geral de objetos 2G. Mas não explorei muito essa ideia. Estou me perguntando se haveria preocupações de desempenho com 100.000 esquemas e milhões de tabelas, visualizações, processos armazenados, etc.

Com tudo isso, aqui está a pergunta específica - quais recursos específicos do SQL Server e/ou estratégias gerais, incluindo, entre outros, coisas que considerei, seriam mais úteis para manter um grande número de conjuntos de dados independentes com esquemas idênticos em um único banco de dados gigante? Reiterando, manter o desempenho o mais próximo possível de uma arquitetura de vários bancos de dados é de alta prioridade.

E nem é preciso dizer que, se alguma parte da minha avaliação acima parecer incorreta ou equivocada, ficarei feliz em ser corrigido. Muito obrigado.

Eu tenho uma cadeia de tabelas pai-filho, por exemplo.

Table A
id 
name

Table B
id
name
parent_a

Table C
id
name
parent_b

Desejo que uma consulta obtenha todas as tabelas em uma única tabela com as seguintes colunas, semelhante ao modelo de lista de adjacência

id
parent_id
name
type

tentei fazer a seguinte consulta

SELECT 
a.id as id,
null AS parent_id,
a.name as name,
1 AS type
FROM a
UNION ALL
SELECT
b.id as id,
b.parent_a AS parent_id,
b.name,
2 AS type
FROM b
UNION ALL
SELECT
c.id as id,
c.parent_b as parent_id,
c.name,
3 AS type
FROM c;

Isso não dá o resultado desejado, pois o id, em cada uma das tabelas individuais, está sendo duplicado, vejo que uma maneira de resolver o problema é incluir uma coluna Guid exclusiva em cada uma das tabelas individuais.

1. O Guid é a única maneira de obter a saída desejada?
2. Em caso afirmativo, existe uma maneira de gerar um Guid exclusivamente em um conjunto de tabelas no postgres?
3. Existe alguma outra maneira de fazer isso sem o Guid?

Minha equipe executa o Cassandra para um aplicativo interno. Esta é a nossa configuração:

• Estamos executando o Cassandra versão 3.11.13.
• Temos vários keyspaces; cada microsserviço tem o seu próprio.
• Temos uma arquitetura ativa em duas regiões da AWS.
• Cada região possui 4 nós: 2 sementes e 2 membros.
• A topologia é um fator de replicação de 4 em cada datacenter/região, portanto, todos os nós possuem 100% dos dados.
• Nossa consistência de leitura é LOCAL_QUORUM e nossa consistência serial é LOCAL_SERIAL, para manter o status ativo-ativo.

Recentemente, tivemos um incidente em que víamos tempos limite de leitura durante consultas do cliente do aplicativo. Eles pareciam estar correlacionados com apenas um keyspace. Depois de muita solução de problemas, descobrimos que nodetool repairo problema foi resolvido. Não tivemos nenhuma exceção nos arquivos de log que nos levasse a entender que havia inconsistência ou corrupção de dados, portanto, só podemos assumir que foi esse o caso porque foi corrigido por um reparo. Também não houve falhas no hardware EC2 ou no sistema operacional que pudessem indicar problemas de disco. Como resultado, não temos uma causa raiz real e não sabemos como evitar que isso aconteça no futuro.

Um sintoma que notamos foi a seguinte mensagem de log que se repetia continuamente para aquele keyspace específico:

INFO  [Native-Transport-Requests-1] 2023-07-04 11:53:15,913 MigrationManager.java:286 - Update Keyspace '[REDACTED]' From KeyspaceMetadata ......

Este sintoma desapareceu após o reparo.

Estamos nos perguntando se alguém tem informações sobre o que pode ter ocorrido e, posteriormente, como evitá-lo.

Pode ser interessante notar que não estávamos realizando reparos com frequência suficiente de acordo com as diretrizes. Fazíamos isso aproximadamente a cada três meses, mas a recomendação é igualar o gc_grace_secondsvalor que você está usando, que atualmente é de 10 dias.

Também pode ser importante saber que suspeitamos que havia um maior grau de carga no momento do incidente devido a um trabalho em lote que estávamos executando periodicamente, mas não vimos sinais de estresse nos nós do Cassandra.

(Eu uso os scripts de Ola Hallengren há um bom tempo)... mas se houver uma consulta aberta, acabo com o seguinte:

Msg 5030, Sev 16, State 12, Line 1: O banco de dados não pôde ser bloqueado exclusivamente para executar a operação. [SQLSTATE 42000] Msg 7926, Sev 16, Estado 1, Linha 1: Instrução de verificação abortada. O banco de dados não pôde ser verificado porque um instantâneo do banco de dados não pôde ser criado e o banco de dados ou tabela não pôde ser bloqueado. Consulte o artigo 928518 da Base de Conhecimento Microsoft

Parâmetros: @Databases = 'USER_DATABASES', @CheckCommands = 'CHECKDB', @PhysicalOnly = 'N', @DataPurity = 'N', @NoIndex = 'N', @ExtendedLogicalChecks = 'N', @TabLock = 'N' , @FileGroups = NULL, @Objects = NULL, @MaxDOP = NULL, @AvailabilityGroups = NULL, @AvailabilityGroupReplicas = 'ALL', @Updateability = 'ALL', @TimeLimit = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'Y' Alguns dos meus bancos de dados não tiveram a verificação concluída há muito tempo. Existe uma maneira de contornar/corrigir esse problema de alguma forma?

Atenciosamente Ian

Eu uso o MySQL versão 8.0.34 em um servidor Debian. Com a atualização mais recente, recebo a mensagem de que mysql_native_password está obsoleto e devo usar caching_sha2_password. Com meus próprios usuários isso não é problema, aqui posso simplesmente alterar o plugin de autenticação.

ALTER USER user
IDENTIFIED WITH caching_sha2_password
BY 'password';

Mas e os usuários integrados mysql.sys, mysql.session e debian-sys-maint? Eu não os criei, eles não se destinam ao login do usuário e, portanto, não tenho uma senha para eles. Como posso mudar para caching_sha2_password aqui?

ALTER USER user
IDENTIFIED WITH caching_sha2_password;

A omissão da senha na instrução SQL resulta apenas em erros.

Obrigado

Li na documentação do MySQL que é possível habilitar a geração de chaves primárias invisíveis ( https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html ).

Sou um novato em administração de banco de dados, então gostaria de entender como adicionar uma chave primária invisível poderia melhorar o desempenho de consultas SELECT se a coluna invisível gerada não for usada nas cláusulas WHERE de tais consultas. E se não consegue melhorar o desempenho, então qual seria o interesse de habilitar esse recurso?

Basicamente, a próxima etapa para o erro "Falha na restrição UNIQUE" com chave primária composta : criei um gatilho após a inserção para definir SEQo maior SEQmais um se for menor que um. Basicamente:

CREATE TRIGGER TRG_NAMES_ADD2 AFTER INSERT ON NAMES FOR EACH ROW
WHEN NEW.SEQ < 1
BEGIN
    UPDATE NAMES SET
        SEQ = (SELECT IFNULL(MAX(SEQ), 0) + 1 FROM NAMES WHERE TYPE = NEW.TYPE)
    WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

No entanto, eu gostaria de fazer a mesma coisa TYPEindependentemente de SEQ. Os problemas que encontrei foram que só posso ter um UPDATEpor gatilho e os AFTER INSERTgatilhos podem ser executados em qualquer ordem.

Eu tentei um segundo gatilho como este:

CREATE TRIGGER TRG_NAMES_ADD1 AFTER INSERT ON NAMES FOR EACH ROW
WHEN NEW.TYPE < 1
BEGIN
    UPDATE NAMES SET
        TYPE = (SELECT IFNULL(MAX(TYPE), 0) + 1 FROM NAMES),
        SEQ = 1
    WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

No entanto, para completar, TRG_NAMES_ADD2devo executar depois TRG_NAMES_ADD1, ou eu teria que colocar ambas as ações (definir TYPEpara um valor possivelmente novo (se <1) e, em seguida, usar esse novo valor para possivelmente definir um novo valor para SEQ(se <1) também) em um gatilho.

Infelizmente isso está além das minhas capacidades.

Por conveniência, aqui está a criação da tabela e alguns exemplos de inserções:

CREATE TABLE NAMES (
TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20),
PRIMARY KEY (TYPE, SEQ)
);

insert into NAMES (type, seq, name) values (2, 3, "T1");
insert into NAMES (type, seq, name) values (2, 0, "T2");
insert into NAMES (type, seq, name) values (2, 0, "T3");
insert into NAMES (type, seq, name) values (0, 1, "T4");
insert into NAMES (type, seq, name) values (0, 0, "T5");

Então as atribuições deveriam ser assim:

T|S|N
-----
2|3|T1  # T,S set manually
2|4|T2  # S set automatically
2|5|T3  # S set automatically
3|1|T4  # T set automatically
4|1|T5  # T,S set automatically

Com pouca prática de SQL(ite), tentei criar uma tabela com uma chave primária composta:

CREATE TABLE NAMES (
  TYPE INTEGER NOT NULL,
  SEQ INTEGER NOT NULL,
  NAME VARCHAR(20),
  PRIMARY KEY (TYPE, SEQ)
);

A ideia é armazenar associações de números para nomes, ou seja: SEQpara NAMEe para trás. Como existe um número variável dessas "classes" acrescentei uma TYPEcoluna, ou seja, que os SEQnúmeros (e NAMEs) devem ser únicos dentro de umTYPE .

Eu também tentei criar um gatilho que atribuirá automaticamente o SEQnúmero "próximo livre" dentro de um específico TYPEcomo este:

CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES
FOR EACH ROW BEGIN
    UPDATE NAMES SET SEQ = (SELECT COUNT(1) FROM NAMES WHERE TYPE = NEW.TYPE)
    WHERE TYPE = NEW.TYPE;
END;

Quando insiro a primeira entrada, parece funcionar, mas quando tento adicionar uma segunda, falha com

Erro de tempo de execução: Falha na restrição UNIQUE: NAMES.TYPE, NAMES.SEQ (19)

assim:

sqlite> select * from NAMES;
sqlite> insert into NAMES (type, seq, name) values (0, -1, "test");             sqlite> select * from NAMES;
0|1|test
sqlite> insert into NAMES (type, seq, name) values (0, -1, "test2");
Runtime error: UNIQUE constraint failed: NAMES.TYPE, NAMES.SEQ (19)
sqlite> insert into NAMES (type, seq, name) values (1, -1, "test2");
sqlite> select * from NAMES;
0|1|test
1|1|test2

Portanto, o gatilho parece funcionar para cada TYPEexatamente uma vez, mas talvez não esteja funcionando, porque o primeiro SEQdeveria ser 0 em vez de 1.

Eu também tentei um gatilho BEFOREem vez de AFTER, mas isso não mudaria SEQtudo (usando -1).

O que há de errado com o gatilho ou é a definição da tabela?

Procedimento armazenado no SQL Server INSERT INTO table1 VALUES A , B

O conjunto de valores A tem como um dos parâmetros: (SELECT TOP 1 cashbalance - 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID)

O conjunto de valores B tem como um dos parâmetros: (SELECT TOP 1 cashbalance + 1 FROM table1 WHERE userid = __ ORDER BY createdUTC, ID)

Quando executado como uma inserção múltipla então a instrução SELECT sóéexecutada uma vez portanto se o id de utilizador for o mesmo para ambos os conjuntos de valores então o campo de saldo de caixa que diz começou em 0 regista -1 na primeira linha inserida e 1 (isto é, não volta a 0 como deveria) na segunda linha inserida. Além disso, o campo createdUTC para ambas as linhas registra exatamente o mesmo valor.

Quando executado como instruções INSERT separadas e sequenciais, o campo cashbalance calcula corretamente, mas o campo createdUTC tem uma pequena lacuna - mas infere a preocupação com outra inserção/leitura entrando.

Obviamente relacionado a bloqueios e/ou serialização - que estou pesquisando e postarei as descobertas (embora sugestões sejam muito bem-vindas). Eu poderia, alternativamente, SELECT SUM(table1.amounts) +/- 1 WHERE userid = __ (e observe que 1 é na verdade uma variável), mas isso não resolve nenhum dos problemas. Além disso, este sp é uma etapa de um processo de quatro sp (mais outro sp de chamada) e isso lança mais complexidade sobre onde implementar quaisquer bloqueios ou trans.

Idealmente, estou tentando implementar contabilidade de entrada dupla (ou seja, cada transação tem uma linha debitando A e outra linha creditando B no mesmo valor simultaneamente) com IDs imediatamente sequenciais e o mesmo createdUTC.