Início / dba / Perguntas / 299203
Accepted
Andrew Savinykh
Asked: 2021-09-08 14:30:42 +0800 CST

A restauração falha no modo único porque o acesso exclusivo não pode ser obtido

  • 772

Temos um script que roda todas as noites, esta é a parte relevante do script:

---- Restore DB from file system
print ''
print '----------------------------------------------'
print 'Restoring Database: $(DbName)'
print '' 
use master
alter database [$(DbName)] set single_user with rollback immediate
restore database [$(DbName)] 
    from  disk = '$(BackupFile)' 
    with replace,
    move @datalogname TO @datapath,
    move @loglogname TO @logpath,
    stats = 5
alter database [$(DbName)] set multi_user
alter database [$(DbName)] set new_broker with rollback immediate
print '----------------------------------------------'

Na maioria das noites funciona. Em algumas noites, ele falha com a seguinte saída:

[04:00:29] :     [Step 2/7] ----------------------------------------------
[04:00:29] :     [Step 2/7] Restoring Database: MYDATABASE
[04:00:29] :     [Step 2/7]  
[04:00:29] :     [Step 2/7] Msg 3101, Level 16, State 1, Server MYSERVER, Line 83
[04:00:29] :     [Step 2/7] Exclusive access could not be obtained because the database is in use.
[04:00:29] :     [Step 2/7] Msg 3013, Level 16, State 1, Server MYSERVER, Line 83
[04:00:29] :     [Step 2/7] RESTORE DATABASE is terminating abnormally.
[04:00:29] :     [Step 2/7] Msg 1205, Level 13, State 68, Server MYSERVER, Line 89
[04:00:29] :     [Step 2/7] Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[04:00:29] :     [Step 2/7] Msg 5069, Level 16, State 1, Server MYSERVER, Line 89
[04:00:29] :     [Step 2/7] ALTER DATABASE statement failed.
[04:00:29] :     [Step 2/7] ----------------------------------------------

A linha 83 é a instrução RESTORE. A linha 89 é a set multi_userinstrução.

Por que isso está acontecendo e como podemos garantir que seja sempre bem-sucedido.

ATUALIZAR

Nenhuma transação explícita é definida no script. O script é executado a sqlcmdpartir de um arquivo. Existem vários processos fora da VM do SQL Server que estão pesquisando o banco de dados regularmente, e meu palpite é que um deles consegue se intrometer no meio set single_usere restorequando isso acontece. Foi sugerido que isso pode ser resolvido ativando temporariamente uma regra no firewall local que proíbe conexões de entrada, mas gostaria de saber se existe uma solução somente SQL.

sql-server sql-server-2017

2 respostas

  1. Normalmente, você deve se conectar ao banco de dados antes de configurá-lo no modo de usuário único para garantir que você seja o usuário único. POR EXEMPLO

    use [$(DbName)]
alter database [$(DbName)] set single_user with rollback immediate

    Mas isso não funciona aqui porque você não pode restaurar pelo banco de dados se estiver conectado a ele. A próxima melhor coisa é usar RESTRICTED_USER em vez de SINGLE_USER

    por exemplo

    alter database [$(DbName)] set restricted_user  with rollback immediate

    Permite que apenas os membros da função de banco de dados fixa db_owner e as funções de servidor fixas dbcreator e sysadmin se conectem ao banco de dados. RESTRICTED_USER não limita seu número. Desconecte todas as conexões com o banco de dados usando o período de tempo especificado pela cláusula de encerramento da instrução ALTER DATABASE. Após a transição do banco de dados para o estado RESTRICTED_USER, as tentativas de conexão de usuários não qualificados são recusadas.

    ALTER DATABASE SET RESTRICTED_USER

    E é claro que você também pode DROP o banco de dados e recriá-lo em vez de restaurá-lo.

    • 2
  2. Best Answer

    Acho que a melhor abordagem aqui é restaurar uma cópia (sempre calculando novos nomes de caminho de arquivo de dados/log com data/hora incorporada para evitar colisões), então você pode simplesmente expulsar os usuários, descartar a versão antiga e renomear a cópia, sem que nenhum atraso associado ao início da restauração seja uma causa potencial para permitir a entrada de conexões persistentes antes que ela possa realmente tentar obter acesso exclusivo:

    RESTORE DATABASE [$(DbName)_copy] 
    FROM DISK = '$(BackupFile)' 
    WITH REPLACE, -- not really necessary, unless last night failed
    MOVE @datalogname TO @datapath, -- change this calc to include date/time
    MOVE @loglogname  TO @logpath,  -- change this calc to include date/time
    STATS = 5;

ALTER DATABASE [$(DbName)_copy] SET NEW_BROKER; -- before anyone can try

ALTER DATABASE [$(DbName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [$(DbName)];

ALTER DATABASE [$(DbName)_copy] MODIFY NAME = [$(DbName)];

    Se isso ainda não o deixar passar, você pode:

    • use um loop para tentar continuamente expulsar os usuários e descartar o banco de dados, por exemplo

      DECLARE @i int = 1;
WHILE @i <= 100
BEGIN
  BEGIN TRY
    ALTER DATABASE [$(DbName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DbName)];
    SET @i = 100;
  END TRY
  BEGIN CATCH
    SET @i += 1;
    PRINT 'failed';
  END CATCH
END

    • desative os logins e/ou crie um gatilho de logon que rejeite temporariamente todos, exceto você (ou pelo menos os reduza) - mas tenha cuidado, é fácil bloquear todos

    • reinicie o serviço SQL Server e simplesmente renomeie o banco de dados em um procedimento de inicialização antes que alguém tenha a chance de se conectar - você pode ter esse procedimento de inicialização sempre ativado e ele verifica um sinalizador definido em algum lugar ou a hora. Na verdade, o procedimento de inicialização pode fazer toda a lógica acima, mas apenas durante o processo de atualização noturna.

    • 1

relate perguntas