.::: How to Shrink Log Database SQL Server :::.

 1.check active transcation

select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION  from sys.databases
 

 if get log_reuse_wait_desc = Replication

EXEC sp_removedbreplication teguhth

SQL Server could not disable distribution on'. (Microsoft.SqlServer.ConnectionInfo)

SELECT spid FROM sys.sysprocesses WHERE dbid = db_id('distribution')

kill 

2. check size from sql
SELECT getdate() as DateCollect,@@servername as ServerName, DB_NAME() AS DbName,
    name AS FileName,
    type_desc,
    size/128 AS CurrentSizeMB,  
    size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128 AS FreeSpaceMB,
    size/128 - (size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128) as UsageSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

SELECT
      getdate() as DateCollect,@@servername as ServerName,DBName = DB_NAME(database_id)
    , CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8 / 1024 AS DECIMAL(8,0)) as LogFileMb
    , CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8 / 1024 AS DECIMAL(8,0)) as DataFileMb
    , CAST(SUM(size) * 8 / 1024 AS DECIMAL(8,0)) as TotalSizeMb
FROM sys.master_files WITH(NOWAIT)
--WHERE database_id = DB_ID() -- for current db
GROUP BY database_id;
 

3. check from windows explorer
 

4. backup log if database set full recovery
USE [AdventureWorks2019]
GO

BACKUP LOG [AdventureWorks2019] TO  DISK = N'nul' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2019-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 
5. shrink log

USE [AdventureWorks2019]
GO
DBCC SHRINKFILE (N'AdventureWorks2017_log' , 0, TRUNCATEONLY)
GO
 

6. Check size from SQL

 

7. check from windows explorer