SQL Server Transaction Log

SQL Server Transaction Log

                                 

                                         SQL Server Transaction Log is main data log file contains in SQL Server Database. If any situation your database crash then SQL Server Transaction Log main recovery option. 

For More details How to recover Delete Data.

• How to check Tr Log Space?

DBCC SQLPERF(LOGSPACE);  GO 

• Is Tr Log Reuse?

SELECT log_reuse_wait_desc FROM sys.databases WHERE [name]='DATABASEUSERGROUP';GO

• Each Database Tr Log Usage detail

SELECT db.[name] AS DBName, db.recovery_model_desc, db.state_desc, db.containment_desc,
db.log_reuse_wait_desc,
CONVERT(decimal(18,2), ls.cntr_value/1024.0) AS LogSizeinMB, CONVERT(decimal(18,2), lu.cntr_value/1024.0) AS LogUsedinMB,
CAST(CAST(lu.cntr_value AS float) / CAST(ls.cntr_value AS float) AS decimal(18,2)) * 100 AS [LogUsed%],
db.[compatibility_level] , db.is_auto_shrink_on
FROM sys.databases AS db WITH (NOLOCK) INNER JOIN
sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.name = lu.instance_name INNER JOIN
sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.name = ls.instance_name LEFT OUTER JOIN
sys.dm_database_encryption_keys AS de WITH (NOLOCK) ON db.database_id = de.database_id
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%';

• Get Number of VLF(virtual Log files)

DBCC LOGINFO

           using this link you can find each database VLF count.

--In SQL Server 2016 and LatestSELECT [name], COUNT(l.database_id) AS 'vlf_count' FROM sys.databases sCROSS APPLY sys.dm_db_log_info(s.database_id) lGROUP BY [name]

;WITH cte_vlf AS (
SELECT ROW_NUMBER() OVER(ORDER BY vlf_begin_offset) AS vlfid, DB_NAME(database_id) AS [Database Name], vlf_sequence_number, vlf_active, vlf_begin_offset, vlf_size_mb
    FROM sys.dm_db_log_info(DEFAULT)),
cte_vlf_cnt AS (SELECT [Database Name], COUNT(vlf_sequence_number) AS vlf_count,
    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 0) AS vlf_count_inactive,
    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS vlf_count_active,
    (SELECT MIN(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_min_vlf_active,
    (SELECT MIN(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS min_vlf_active,
    (SELECT MAX(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_max_vlf_active,
    (SELECT MAX(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS max_vlf_active
    FROM cte_vlf
    GROUP BY [Database Name])
SELECT [Database Name], vlf_count, min_vlf_active, ordinal_min_vlf_active, max_vlf_active, ordinal_max_vlf_active,
((ordinal_min_vlf_active-1)*100.00/vlf_count) AS free_log_pct_before_active_log,
((ordinal_max_vlf_active-(ordinal_min_vlf_active-1))*100.00/vlf_count) AS active_log_pct,
((vlf_count-ordinal_max_vlf_active)*100.00/vlf_count) AS free_log_pct_after_active_log
FROM cte_vlf_cnt
GO 

If database is Recovery mode is FULL then your database Tr Log growth will depend on your database usage. if high usage database then Tr Log growth  will be high.

Then first you need to take full backup of the database as follows,

BACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP3.trn' WITH NOFORMAT, INIT,  NAME = N'DATABASEUSERGROUP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10GODECLARE @backupSetId AS intSELECT @backupSetId = position FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' and backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' )IF @backupSetId is null BEGIN  RAISERROR(N'Verify failed. Backup information for database ''DATABASEUSERGROUP'' not found.', 16, 1) ENDRESTORE VERIFYONLY FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP3.trn' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND;GO

 or else you can schedule your full backup daily or weekly depend on your need.
next you need to log backup depend on your database usage (Ex: hourly log backups)

BACKUP LOG [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP3.trn' WITH NOFORMAT, NOINIT,  NAME = N'DATABASEUSERGROUP-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10GO

If you no need to keep log file then you can truncate Tr Log as follows,
(Not Recommended to used in Production Database if has backup Maintain Plan)

USE [DATABASEUSERGROUP]GODBCC SHRINKFILE (N'DATABASEUSERGROUP_log' , 0, TRUNCATEONLY)GO

Hope you will have good understand of this article.   
Cheers...
and Most welcome your comments and ideas...