Short Scripts – Fevereiro 2018 – Transaction Log

Olá, bom dia, mais uma semana começando….

E você já esta aqui acessando o meu blog, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 32 posts, sendo estes publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Mas como este é o primeiro post desta sessão em 2018, farei algo um pouco diferente, você terá uma pequena surpresa.

Desejo que o conteúdo aqui compartilhado possa lhe ser útil, como também sirvo de referência e sugestões para novas formar de resolução de problemas e aprendizado.

Vamos então conhecer um pouco mais sobre este novo post….

O post de hoje

Como já destacado no início do post, ao invés de compartilhar os últimos scripts adicionados a  minha biblioteca, quero dividir com você um conteúdo dedicado especificamente a um assunto muito importante quando nos referimos ao Microsoft SQL Server, mais especificamente ao Transaction Log (Log de Transações), funcionalidade presente em todos os bancos de dados criados em qualquer versão e edição do SQL Server.

E ai que você achou desta surpresa, gostou? Eu gostei, não é fácil você conseguir encontrar em um único local um conteúdo focado exclusivamente a este assunto tão importante, que muitos profissionais que trabalham com banco de dados até hoje não conseguem entender o conceito e forma de atuação do Transaction-Log.

Seguindo em frente, a seguir apresento os códigos e exemplos selecionados para o Short Script – Fevereiro 2018 – Transaction Log. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Short Scripts

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos:

— Short Script 1 – Log Record —

— Altera o Recovery Model para SIMPLE
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Truncar o Transaction Log —
CHECKPOINT
Go

— Conteúdo do log – todas as colunas —
USE AdventureWorks2016
Select * from ::fn_dblog(null, null)
Go

–update
Begin Transaction

UPDATE dbo.Pessoa
SET nome = ‘XUXA’
Where ID=3

Rollback
Go

— Conteúdo armazenado no Log File —
Select [Current LSN],
Operation,
Context,
[Transaction ID],
[Log Record Length],
[Previous LSN],
AllocUnitName,
[Page ID],
[Slot ID],
[Checkpoint Begin],
[Checkpoint End],
[Minimum LSN],
SPID,
[Begin Time],
[Transaction Name],
[Parent Transaction ID],
[Lock Information],
Description,
[RowLog Contents 0],
[RowLog Contents 1],
[Log Record]
From ::fn_dblog(null, null)
Go

— DBCC SQLPERF —
DBCC SQLPERF(LOGSPACE)
Go

— Short Script 2 – CheckPoint —

— Criando a Base de Dados —
CREATE DATABASE DemoCheckpoint
ON PRIMARY
(NAME = ‘DemoCheckpoint_data’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_data.mdf’)
LOG ON
(Name = ‘DemoCheckpoint_Log’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_log.ldf’,
SIZE = 100MB,
FILEGROWTH = 10MB)
GO

— Alterando o Recovery Model —
ALTER DATABASE DemoCheckpoint
SET RECOVERY SIMPLE
Go

— Criando a Tabela Teste —
USE DemoCheckpoint
GO

CREATE TABLE Teste
(C1 varchar(50) NOT NULL,
C2 varchar(50) NOT NULL)
GO

— Forçando o Checkpoint —
CHECKPOINT
Go

— Abrir o Perfmon com os contadores

— em outra sessão
USE DemoCheckpoint
GO

WHILE 1=1
BEGIN

INSERT INTO dbo.teste
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Short Script 3 – Log Chain Simple —

— Iniciar nova sessão do Perfmon —
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Alterar Recovery Model para Simple —
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Abrir nova Query —
USE AdventureWorks2016
Go

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Alterar Recovery Model para Full —
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
GO

— Realizar Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backupcompress.bak’
WITH COMPRESSION,
DIFFERENTIAL
GO

— Short Script 4 – DBCC LogInfo —

— Criando uma nova base de dados —
CREATE DATABASE TestDB
ON PRIMARY
(NAME = ‘TestDB_data’,
FILENAME = ‘D:\MSSQL\TestDB_data.mdf’)
LOG ON
(Name = ‘TestDB_Log’,
FILENAME = ‘D:\MSSQL\TestDB_log.ldf’,
SIZE = 10MB,
FILEGROWTH = 10MB)
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

–Forçando o crescimento do Transact-Log manualmente em 20MB —
ALTER DATABASE TestDB
MODIFY FILE
(NAME = ‘TestDB_Log’,
SIZE = 20MB);
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

— Short Script 5 – Natureza Circular —

— Alterando Recovery Model FULL —
ALTER DATABASE TestDB
SET RECOVERY FULL;
Go

— Realizando Backup Database —
BACKUP DATABASE TestDB
TO DISK = ‘D:\TestDB.bak’
Go

— Forçando o encolhimento do Transaction – Log —
DBCC LOGINFO(TestDB)
Go

BACKUP LOG TestDB
TO DISK = ‘bkplogTestDB.trn’
Go

USE TestDB
Go

DBCC SHRINKFILE (TestDB_Log,1)
Go

DBCC LOGINFO(TestDB)
Go

— Criando uma nova Tabela —
USE TestDB
GO

CREATE TABLE dbo.pessoa
(ID int identity PRIMARY KEY NOT NULL,
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL,
Nascimento date NOT NULL,
Cargo varchar(50))
GO

— Abrir nova query —
USE TestDB
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘Junior’, ‘Galvão’, ‘19800428’, ‘Database Administrator’)
END

— Monitorar o crescimento do log em tempo de execução —
DBCC LOGINFO(TestDB)

CHECKPOINT

SELECT name,
Log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘TestDB’
Go

— Realizar Backup do Arquivo de Log —
BACKUP LOG TestDB TO DISK = ‘d:\log.trn’
Go

— Alterando Recovery Model para Full
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
Go

— Realizando novo Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
WITH COMPRESSION
Go

— Short Script 6 – Backup and Transaction Log —

— Preparando a base – 1m10s se não preparada na demo 5
ALTER DATABASE AdventureWorks2016 SET RECOVERY FULL
GO
BACKUP DATABASE AdventureWorks2016 TO DISK = ‘d:\backup.bak’ WITH COMPRESSION
GO

— Realizando Backup do Arquivo de Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Encolhendo o Transaction Log —
USE AdventureWorks2016
Go

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Ajustando o tamanho do Transaction Log —
USE AdventureWorks2016
Go

— Encolhendo o Transaction Log —
DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Modificando o arquivo de Log —
ALTER DATABASE AdventureWorks2016
MODIFY FILE
(NAME = AdventureWorks2016_Log,
SIZE = 4MB)
Go

— Encolhendo o Transaction Log —
DBCC LOGINFO(AdventureWorks2016)

— Abrir nova query —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
Go

— Abrir nova query —
USE AdventureWorks2016
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END
Go

— Forçando o Truncate do Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtedo informações do arquivo de log —
DBCC LOGINFO(AdventureWorks2016)
CHECKPOINT
SELECT name,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Ajustando o tamanho do TLog
USE AdventureWorks2016
GO

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
GO

DBCC LOGINFO(AdventureWorks2016)
Go

— Short Script 7 – File Growth —

— Habilitando Trace Flags para evidênciar mudanças no Log —
DBCC TRACEON (3004, 3605, -1);
Go

— Limpar o log do SQL Server —
sp_cycle_errorlog
Go

— Criar uma nova Base de Dados —
CREATE DATABASE TransactionLog
ON PRIMARY
(NAME = ‘TransactionLog_data’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_data.mdf’,
SIZE = 10240MB)
LOG ON
(Name = ‘TransactionLog_Log’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_log.ldf’,
SIZE = 1024MB,
FILEGROWTH = 1024MB)
GO

— Identificar o tempo decorrido para processamento relacionado somente ao Log —
xp_readerrorlog
Go

— Desabilitando Trace Flags —
DBCC TRACEOFF (3004, 3605, -1);
Go

— Short Script 8 – Open Transaction in Tracking —

— Identificando as Transações abertas e seu respectivo consumo do Transaction Log —
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions [s_tdt] Inner JOIN sys.dm_tran_session_transactions [s_tst]
ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
Inner JOIN sys.[dm_exec_sessions] [s_es]
ON [s_es].[session_id] = [s_tst].[session_id]
Inner JOIN sys.dm_exec_connections [s_ec]
ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er]
ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY [Begin Time] ASC;
Go

É isso ai, missão cumprida! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

Quero agradecer ao amigo Luiz Mercante que colaborou com a criação e uso destes scripts em algumas palestras realizadas nos últimos anos no evento SQL Saturday.

---

Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/

https://pedrogalvaojunior.wordpress.com/2017/03/31/short-scripts-marco-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de maio de 2018.

Sucesso….