Por: Sergey Gigoyan | Atualizado em: 2015-11-18 | Comentários (9) | Relacionados: Mais > Administração de Banco de dados
Problema
SQL Server permite usar mais de um arquivo de log de transação, mas a questão que surge é se ele é necessaryand qual é a vantagem de ter vários arquivos de log de transação. Há um equívoco entre alguns desenvolvedores de que ter arquivos de log multipletransaction pode aumentar o desempenho porque o SQL Server pode usá-los em paralelo. O SQL Server usaapenas um arquivo de log de transações no momento e não há paralelismo neste caso. Às vezes, mais de um arquivo de log pode sernecessário para fins de solução de problemas. Portanto, normalmente não há necessidade de ter mais de um arquivo de log.Vamos considerar um caso, quando nosso banco de dados tem mais de um arquivo de log e devemos reter apenas um.Esta dica tem como objetivo descrever como remover arquivos de log desnecessários corretamente e reter apenas um.
Solução
Antes de começar a ilustrar como remover arquivos de log desnecessários, vamos descrever brevemente como o SQL Server workswith arquivos de log: quando o banco de dados tem mais de um arquivo de log, o SQL Server continua gravando no primeiro até que esteja cheio,depois muda para o segundo e assim por diante. Depois que o último arquivo de log se tornar o SQL Server completo retorna ao primeiro e o ciclo continua.No entanto, como mencionamos, às vezes mais de um arquivo de log pode ser necessário. Por exemplo, quando o disco onde o arquivo de log é locatedbecomes completo e precisamos criar o segundo arquivo de log em outro local, mas depois de solucionar o problema,devemos excluir o segundo arquivo de log, porque não adianta ter mais de um arquivo de log.Agora, vamos supor que temos um banco de dados com dois arquivos de log e nossa tarefa é remover o segundo.O script a seguir cria o banco de dados TestDB com dois arquivos de log e tabela TestTable. Para executá-lo sozinho, você precisará substituir”D:\SQL dados” com um caminho de pasta existente.
USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL
agora vamos entender a estrutura física do log de transações: o arquivo de log de transações internamente consiste Emarquivos de Log virtual (VLF), que são a unidade de gerenciamento no arquivo de log de transações. Isso significa que quando o mecanismo de banco de dados cresce ou reduzo arquivo de log, ele faz isso com VLFs completos (por exemplo, não pode encolher metade do VLF). O tamanho dos arquivos de log virtuais, bem comoseu número no arquivo de log físico não é corrigido e é gerenciado dinamicamente pelo mecanismo de banco de dados. Para monitorar o arquivo de log de transações internamenteusamos o comando” DBCC LOGINFO”, que fornece informações sobre arquivos de log virtuais. No script abaixo usamos este comando para nosso banco de dados:
--Getting log files infoDBCC LOGINFO('TestDB')
E o resultado é o seguinte:
FileID é IDs de arquivo de log para nosso banco de dados. Status indica é VLF reutilizável ornot (valores possíveis: 0-SIM, 2-não). Como podemos ver, há apenas um VLF withStatus = 2. Agora, quando vamos inserir dados em TestTable e monitorizar a forma como os arquivos de log estão crescendo:
USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n
Com este exemplo podemos ver que ambos os ficheiros de registo cresceu e agora há Vlf com “Status=2” na segunda log de arquivo (FileID=3), também:
Agora precisamos remover TestDB_log2.arquivo ldf. Observe que podemos remover apenas os arquivos de log secundários. Remover o arquivo de log primário não é permitido pelo SQL Server.Cada banco de dados tem apenas um arquivo de log primário e o primeiro arquivo de log que é criado Emo script de criação de banco de dados é considerado o principal.Se tentar remover o segundo arquivo de log:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
Vamos receber a seguinte mensagem de:
Nós podemos remover o arquivo de log de transação somente quando ele está vazio, portanto wefirst necessário para esvaziar.Para fazer isso, nós deve fazer backup do log de transações. Desde a nossa “TestDB” banco de dados isnewly criado e não há backups completos, nós needto emitir um completo banco de dados de backup para o banco de dados TestDB, após o que canissue um backup de log de transação:
--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'
O backup de log de transação trunca o arquivo de log (há algumas exceções, que estão fora do escopo de thistip). O truncamento de Log exclui arquivos de log virtuais inativos deo início do log lógico e libera espaço no arquivo de log. No entanto, o truncamento não reduz o tamanho de um arquivo de log físico. Ele só libera espaço nele, que pode ser reutilizado. Vamos executar “DBCC LOGINFO” novamente:
Como podemos ver não há arquivos de log virtuais no “TestDB_log2.ldf ” arquivo com Status = 2 e agora nosso arquivo de log está vazio e pronto para remoção:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
a remoção é bem sucedida.
Entretanto, quando se verifique o log as informações de novo, vamos ver que a lógica do arquivo de log ainda existe:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Se fizermos outro de backup de log, o arquivo será excluído:
--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Próximos Passos
- Manter esta dica em mente ao determinar o uso do log de transações e como remover anunneeded arquivo de log.
- confira estes recursos:
- Administração de Banco de dados | Log de Transação
- https://technet.microsoft.com/en-us/library/ms191433(v=sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms179355(V = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms345414(V = sql.105).aspx
Sobre o autor
Sergey Gigoyan é um banco de dados de profissionais com mais de 10 anos de experiência, com foco em design de banco de dados, desenvolvimento, ajuste de desempenho, otimização, alta disponibilidade, BI e DW design.Ver todas as minhas dicas
Artigo Última atualização: 2015-11-18