Início / coding / Perguntas / 79420764
Accepted
Trying to be DBA
Asked: 2025-02-07 19:44:31 +0800 CST

Script para obter o nome do arquivo de log do banco de dados, alterar a recuperação completa para simples, reduzir o arquivo de log e definir de volta para recuperação completa

  • 772

Estou tentando criar um script para obter o nome do arquivo de log de um banco de dados, definir como recuperação simples, reduzir o arquivo de log desse banco de dados e definir novamente como completo.
Até agora, eu criei este script:

DECLARE @RestoreDbName nvarchar(2000);
declare @Name nvarchar(2000)

set @restoreDbName = 'dbname'


DECLARE cur CURSOR FOR
SELECT name
FROM [sys].[database_files] 
where [type] = 1 and name like @restoreDbName

ALTER DATABASE @RestoreDbName
SET RECOVERY SIMPLE;  
GO 

OPEN cur
FETCH NEXT FROM cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC SHRINKFILE(@Name, 1)
    FETCH NEXT FROM cur INTO @Name
END
CLOSE cur
DEALLOCATE cur
GO  
       
 ALTER DATABASE @RestoreDbName
 SET RECOVERY FULL;  
 GO

O script será executado a partir de um PowerShell, não gera nenhuma mensagem de erro, mas quando verifico o arquivo de log do banco de dados continua o mesmo de antes, não foi reduzido.

Alguma sugestão, o que estou fazendo errado?

sql-server

1 respostas

  1. Best Answer

    Você precisa confirmar se o resultado da seguinte declaração é verdadeiro NOTHINGantes de tentar reduzir o log.

    SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = N'YourDb';

    Se não for, NOTHINGentão algo está segurando o log e impedindo o truncamento. Veja este post .

    Você também deve considerar por que o log ficou tão grande em primeiro lugar: se você não precisa de backups de log, então por que mantê-lo no FULLmodo? E por que reduzi-lo se ele vai precisar de espaço novamente?

    Você também tem alguns problemas com seu script:

    • Adicione CHECKPOINTdepois da primeira SET RECOVERY SIMPLEdeclaração.
    • ALTER DATABASEnão é parametrizável, então você precisa usar SQL dinâmico.
    • Você precisa filtrar a database_filesconsulta para obter apenas os arquivos de log.
    • O cursor pode ser uma variável local, o que elimina a necessidade de desalocação manual.
    • Use STATICcursores em visualizações do sistema, pois eles podem ser instáveis.
    • O tamanho de log de 1 Mb é quase certamente muito pequeno, mesmo para bancos de dados minúsculos. Considere um tamanho mais adequado.
    DECLARE @RestoreDbName sysname = 'dbname';
DECLARE @sql nvarchar(1000) = N'
ALTER DATABASE ' + QUOTENAME(@RestoreDbName) + '
SET RECOVERY SIMPLE;

CHECKPOINT;

DECLARE @Name sysname;
DECLARE @crsr CURSOR;

SET @crsr = CURSOR LOCAL STATIC FOR
SELECT name
FROM ' + QUOTENAME(@RestoreDbName) + '.sys.database_files
where type = 1
  and type_desc = ''LOG'';

OPEN @crsr;

WHILE 1=1
BEGIN
  FETCH NEXT FROM @crsr INTO @Name;
  IF @@FETCH_STATUS <> 0 BREAK;
  
    DBCC SHRINKFILE(@Name, 10)
END;
       
ALTER DATABASE ' + QUOTENAME(@RestoreDbName) + '
SET RECOVERY FULL;
';

EXEC sp_executesql @sql,
  N'@RestoreDbName sysname',
  @RestoreDbName;
    • 1

relate perguntas