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;
