SQL ServerVideos
2K
0

Cómo reducir tu log de transacciones

El log de transacciones es fundamental para tu base de datos SQL.

Muchas veces crece mucho y no podemos achicarlo, por ese motivo he armado el siguiente video tutorial en donde no solo te explico el funcionamiento del log sino que también cómo poder monitorearlo, saber porque no se achica y controlarlo.

Enlaces

The Transaction Log (SQL Server) – SQL Server | Microsoft Learn

SQL Server transaction log architecture and management guide – SQL Server | Microsoft Learn

Ejemplo utilizado en el video

--- DEMO REDUCIR TLOG
USE MASTER
GO

DROP DATABASE [DEMOTLOG]

CREATE DATABASE [DEMOTLOG]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DEMOTLOG', FILENAME = N'F:\SQL\SystemDB\DEMOTLOG.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'DEMOTLOG_log', FILENAME = N'F:\SQL\SystemDB\DEMOTLOG_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB )
 WITH LEDGER = OFF
GO

BACKUP DATABASE [DEMOTLOG] 
TO  DISK = N'F:\SQL\MSSQL15.MSSQLSERVER\MSSQL\Backup\DEMOTLOG.bak' WITH 
INIT,  NAME = N'DEMOTLOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


USE [DEMOTLOG]

SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 

-- MODO SIMPLE

USE [master]
GO
ALTER DATABASE [DEMOTLOG] SET RECOVERY SIMPLE 

USE [DEMOTLOG]
GO
DBCC SHRINKFILE (N'DEMOTLOG_log' , 1)
GO


--- HACEMOS UN INSERT
USE DEMOTLOG 
GO

SELECT TOP (1) C1.* 
INTO DBO.TABLA_1
FROM 
MASTER..SYSCOLUMNS C1
CROSS JOIN MASTER..SYSOBJECTS 

declare @n int = 1

while @n <=30000
begin
 insert into DBO.TABLA_1
 SELECT TOP (5) C1.* 
 FROM 
 MASTER..SYSCOLUMNS C1
 CROSS JOIN MASTER..SYSOBJECTS 

 set @n = @n + 1
end

SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 


SELECT log_reuse_wait_desc  
FROM SYS.DATABASES
WHERE NAME= 'DEMOTLOG'

-- checkpoint

SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 


USE [DEMOTLOG]
GO
DBCC SHRINKFILE (N'DEMOTLOG_log' , 10)
GO

SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 


----- modo full

USE [master]
GO
ALTER DATABASE [DEMOTLOG] SET RECOVERY FULL 
GO

BACKUP DATABASE [DEMOTLOG] 
TO  DISK = N'F:\SQL\MSSQL15.MSSQLSERVER\MSSQL\Backup\DEMOTLOG.bak' WITH 
INIT,  NAME = N'DEMOTLOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


USE [DEMOTLOG]
GO
DBCC SHRINKFILE (N'DEMOTLOG_log' , 10)
GO


SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 


declare @n int = 1

while @n <=30000
begin
 insert into DBO.TABLA_1
 SELECT TOP (5) C1.* 
 FROM 
 MASTER..SYSCOLUMNS C1
 CROSS JOIN MASTER..SYSOBJECTS 

 set @n = @n + 1
end


USE DEMOTLOG 


SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 

SELECT log_reuse_wait_desc  
FROM SYS.DATABASES
WHERE NAME= 'DEMOTLOG'




BACKUP LOG [DEMOTLOG] TO  DISK = 
N'F:\SQL\MSSQL15.MSSQLSERVER\MSSQL\Backup\DEMOTLOG.trn' WITH NOFORMAT, INIT,  NAME = N'DEMOTLOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP LOG [DEMOTLOG] TO  DISK = 
N'F:\SQL\MSSQL15.MSSQLSERVER\MSSQL\Backup\DEMOTLOG.trn' WITH NOFORMAT, INIT,  NAME = N'DEMOTLOG-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

SELECT log_reuse_wait_desc  
FROM SYS.DATABASES
WHERE NAME= 'DEMOTLOG'


SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 

SELECT log_reuse_wait_desc  
FROM SYS.DATABASES
WHERE NAME= 'DEMOTLOG'



USE [DEMOTLOG]
GO
DBCC SHRINKFILE (N'DEMOTLOG_log' , 20)
GO

SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 

You must be logged in to post a comment.