We recently were involved in a case that had SQL Server 2016 Standard running with Always On Availability Groups, Transactions Replication and Log Shipping. One of the User Databases had a Transaction Log file that had grown to 800+GB. The below T-SQL was used to help resolve the issue. In this case there were no Transaction Log Backups being taken and the database was in Full recovery model. Solution here was to manually take the first (huge) Transaction Log Backup and then schedule regular T-Log backups.
T-SQL: Queries to provide information on the Transaction Log
(with some notes included)
Declare @dbname sysname
Set @dbname = 'xyz'
--Find reason for Log Re-Use wait:
Select
Name as 'Database Name',
Log_Reuse_Wait_Desc
from
sys.databases
where Name = @dbname
order by name
--Full Recovery Model needed for AlwaysON
--Backup Log - then able to resuse log space
--DBCC ShrinkFile
--May need to run backup log and shrink multiple times if high # of VLFs in log file
--Shrink to smallest size possible, then resize to ~500MB to avoid future auto-grows
--Find Log File Sizes:
Select
DB_NAME() AS 'Database Name',
Physical_Name as 'Physical Name',
Name AS 'File Name',
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
*
FROM sys.database_files
Where DB_Name() = @dbname;
--Find number of VLFs:
DBCC LOGINFO (@dbname)
--# Rows = # VLF's
--Recommended to keep less than 100 VLF's; especially with Log Shipping involved
--Find Backup Information:
Select
Database_Name,
msdb.dbo.backupset.backup_set_id,
msdb.dbo.backupset.last_recovery_fork_guid,
msdb.dbo.backupset.database_guid,
software_name, physical_device_name, backup_start_date, backup_finish_date,
CASE type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File/filegroup'
END as type,
CASE type
WHEN 'I' THEN differential_base_lsn
ELSE first_lsn
END as effective_first_lsn,
last_lsn
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
INNER JOIN msdb.dbo.backupmediaset ON msdb.dbo.backupset.media_set_id = msdb.dbo.backupmediaset.media_set_id
WHERE [database_name] = @dbname
ORDER BY [database_name], msdb.dbo.backupset.backup_set_id DESC
--Find Log Backup Size:
--(or 'D' for Database Backup Sizes)
Declare @db sysname = 'xyz';
Declare @type varchar(10) = 'L';
Declare @durationindays int = 7
Select
bs.Server_Name,
bs.Database_Name,
Case bs.Type When 'L' Then 'Log' When 'D' Then 'Database' End as Type,
bs.backup_size 'BackupSize(B)',
(bs.backup_size/1024) 'BackupSize(KB)',
(bs.backup_size/1024)/1024 'BackupSize(MB)',
(bs.backup_size/1024)/1024/1024 'BackupSize(GB)',
bs.Backup_Start_Date,
bs.Backup_Finish_Date ,
bf.Physical_Device_Name,
bs.Recovery_Model,
bs.User_Name
FROM
MSDB..backupset bs
INNER JOIN msdb..backupmediafamily bf ON bf.media_set_id=bs.media_set_id
where
bs.type = @type and
bs.database_name = @db and
bs.backup_finish_date > GetDate() - @durationindays
order by backup_finish_date desc
--Find Backup Log Completion Times:
SELECT
d.PERCENT_COMPLETE AS [% Complete],
d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
convert(decimal(18,5),d.TOTAL_ELAPSED_TIME*0.000000277) AS ElapsedTimeHours,
d.ESTIMATED_COMPLETION_TIME/60000 AS TimeRemainingMin,
convert(decimal(18,5),d.ESTIMATED_COMPLETION_TIME*0.000000277) AS TimeRemainingHours,
s.text AS Command
FROM sys.dm_exec_requests d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
WHERE d.COMMAND LIKE '%Backup%'
ORDER BY 2 desc, 3 DESC
--Log_Reuse_Wait_Desc = LOG_BACKUP
--Backup Transaction Log File
--BACKUP LOG [xyz] TO DISK = N'E:\xyzlog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'xyx-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
GO
--Shrink Transaction Log File
--DBCC ShrinkFile ('xyz_Log', 500)
GO
Notes:
Log Shipping takes its own log backup. You cannot have another log backup job for log shipped database as it will break the log chain. You need to check time to time to make sure log shipping log backup is working fine.
If the database is not log shipped, you should have log backup job scheduled for each database with full recovery model. All AG databases are of full recovery model.
|
|
|
|
|
|