Transaction Log Management
The senior DBA challenged the junior DBA guys to write a Transaction Log script to help us troubleshoot TLog issues.
I used sys.databases and DBCC sqlperf(logspace) as a basis for my script.
Here is the script I came up with.
USE master
go
/****************************************************
script is used to help quickly troubleshoot issues with the log
Queries sys.database, sys.master_files and dbcc sqlperf(logspace) to find log space usage
will return a shrinkfile statement to shrink log
****************************************************/
IF OBJECT_ID('tempdb..#TempForLogSpace') IS NOT NULL
BEGIN
DROP TABLE #TempForLogSpace
END
CREATE TABLE #TempForLogSpace
(
DataBaseName VARCHAR(100) ,
LogSize NUMERIC(18, 4) ,
LOgPercentage NUMERIC(18, 4) ,
Status INT
)
INSERT INTO #TempForLogSpace
EXEC ( 'DBCC sqlperf(logspace)'
)
SELECT t.DataBaseName ,
smf.name AS 'Logical Log File Name' ,
t.LogSize / 1024 AS 'Log Size (GB)' ,
t.LOgPercentage AS 'Log Space Used(%)' ,
sd.log_reuse_wait_desc AS Log_Desc ,
'Use ' + t.DataBaseName + ';' + CHAR(13) + 'DBCC SHRINKFILE ('
+ SMF.NAME + ' , 1024)' AS SHRINKFILE ,
t.LogSize AS 'Log Size (MB)' ,
t.LOgPercentage * t.LogSize / 100 AS 'Log Space Used (MB)' ,
smf.physical_name AS Log_File_Location
FROM #TempForLogSpace AS t
INNER JOIN sys.databases AS sd ON t.DataBaseName = sd.name
INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
WHERE smf.type_desc <> 'rows'
ORDER BY t.LogSize DESC
====================================================================================================================================================================================================================
==========================================================================================================
==========================================================================================================
Here is version two of the above process. This will return log info for a specific DB as well as directions on how to proceed and all the statements needed to fix the issue are also generated. I provided this for a customer who had no DBA but they were a financial services company so I couldn't access their environment.
DECLARE @backuploc VARCHAR(200);
USE msdb;
SELECT @backuploc = REVERSE(SUBSTRING(REVERSE(physical_device_name),
CHARINDEX('\',
REVERSE(physical_device_name)),
LEN(physical_device_name)))
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE database_name = 'qfiniti_platform'
AND type = 'L'
AND backup_start_date = ( SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset
);
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#TempForLogSpace') IS NOT NULL
BEGIN
DROP TABLE #TempForLogSpace;
END;
CREATE TABLE #TempForLogSpace
(
DataBaseName VARCHAR(100) ,
LogSize NUMERIC(18, 4) ,
LOgPercentage NUMERIC(18, 4) ,
Status INT
);
INSERT INTO #TempForLogSpace
EXEC ( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS'
);
SELECT smf.name AS 'Logical Log File Name' ,
CASE WHEN smf.is_percent_growth = 0
THEN 'file is set to a fixed value and is considered best practice'
WHEN smf.is_percent_growth = 1
THEN 'See modify_log_file_statement---->'
END AS log_growth_setting ,
CASE WHEN smf.is_percent_growth = 0 THEN '--no alter DB statement needed'
WHEN smf.is_percent_growth = 1
THEN 'ALTER DATABASE '+sd.name + CHAR(13)
+ 'MODIFY FILE ' + CHAR(13) + '(NAME = ''' + smf.name
+ ''',' + CHAR(13) + 'FILEGROWTH = 512MB)'
END AS Modify_Log_File_statement ,
t.LogSize / 1024 AS 'Log Size (GB)' ,
t.LOgPercentage AS 'Log Space Used(%)' ,
CASE WHEN sd.log_reuse_wait_desc = 'LOG_BACKUP'
THEN 'please backup the log---->'
WHEN sd.log_reuse_wait_desc = 'ACTIVE_TRANSACTION'
THEN 'you have an active transaction..it must complete or be killed before log will clear'
ELSE 'no log backup required'
END AS Log_Backup_Action ,
sd.log_reuse_wait_desc ,
'Backup log '+sd.name +' to disk = ''' + ISNULL(@backuploc,'c:\')
+sd.name +'_Tlog_backup.trn'' with stats = 5' AS Backup_Statement ,
'Use ' + t.DataBaseName + ';' + CHAR(13) + 'DBCC SHRINKFILE ('
+ smf.name + ' , 1024)' AS SHRINKFILE_statement ,
'Alter Database'+sd.name+' SET Recovery Simple' AS Change_Recovery_Model_to_Clear_log ,
smf.physical_name AS Log_File_Location
FROM #TempForLogSpace AS t
INNER JOIN sys.databases AS sd ON t.DataBaseName = sd.name
INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
WHERE smf.type_desc <> 'rows'
AND sd.database_id > 4;
PRINT 'this process will give you the statements to fix the log_file issue..'+CHAR(13)+CHAR(13)+
'if the log file is set to grow by a percent the script will provide you the statement to change to fixed value of 512MB..'+CHAR(13)+
'if log_growth_setting is "see modify_log_file_statement---->" then copy the Modify_Log_File_Statement and execute..'+CHAR(13)+CHAR(13)+
'Log_Backup_Action column will advise you on if a backup is required..'+CHAR(13)+
'if Log_Backup_Action returns "please backup the log---->" then copy the backup_statement and execute'+CHAR(13)+CHAR(13)+
'After you take a backup rerun this process and see if the Log Space Used(%) is now at an acceptable level, it should be under 5% if there are no active transactions.. '+CHAR(13)+
'If the Log Space Used(%) is at an acceptable level you can now execute the SHRINKFILE_Statement. Depending on how large the file is, this could take some time.. '+CHAR(13)+CHAR(13)+
'If you find this overwhelming or overly confusing you can execute the Change_Recovery_Model_to_Clear_log statement and then the SHRINKFILE_Statement..'
USE master
GO
I used sys.databases and DBCC sqlperf(logspace) as a basis for my script.
Here is the script I came up with.
USE master
go
/****************************************************
script is used to help quickly troubleshoot issues with the log
Queries sys.database, sys.master_files and dbcc sqlperf(logspace) to find log space usage
will return a shrinkfile statement to shrink log
****************************************************/
IF OBJECT_ID('tempdb..#TempForLogSpace') IS NOT NULL
BEGIN
DROP TABLE #TempForLogSpace
END
CREATE TABLE #TempForLogSpace
(
DataBaseName VARCHAR(100) ,
LogSize NUMERIC(18, 4) ,
LOgPercentage NUMERIC(18, 4) ,
Status INT
)
INSERT INTO #TempForLogSpace
EXEC ( 'DBCC sqlperf(logspace)'
)
SELECT t.DataBaseName ,
smf.name AS 'Logical Log File Name' ,
t.LogSize / 1024 AS 'Log Size (GB)' ,
t.LOgPercentage AS 'Log Space Used(%)' ,
sd.log_reuse_wait_desc AS Log_Desc ,
'Use ' + t.DataBaseName + ';' + CHAR(13) + 'DBCC SHRINKFILE ('
+ SMF.NAME + ' , 1024)' AS SHRINKFILE ,
t.LogSize AS 'Log Size (MB)' ,
t.LOgPercentage * t.LogSize / 100 AS 'Log Space Used (MB)' ,
smf.physical_name AS Log_File_Location
FROM #TempForLogSpace AS t
INNER JOIN sys.databases AS sd ON t.DataBaseName = sd.name
INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
WHERE smf.type_desc <> 'rows'
ORDER BY t.LogSize DESC
====================================================================================================================================================================================================================
==========================================================================================================
==========================================================================================================
Here is version two of the above process. This will return log info for a specific DB as well as directions on how to proceed and all the statements needed to fix the issue are also generated. I provided this for a customer who had no DBA but they were a financial services company so I couldn't access their environment.
DECLARE @backuploc VARCHAR(200);
USE msdb;
SELECT @backuploc = REVERSE(SUBSTRING(REVERSE(physical_device_name),
CHARINDEX('\',
REVERSE(physical_device_name)),
LEN(physical_device_name)))
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE database_name = 'qfiniti_platform'
AND type = 'L'
AND backup_start_date = ( SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset
);
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#TempForLogSpace') IS NOT NULL
BEGIN
DROP TABLE #TempForLogSpace;
END;
CREATE TABLE #TempForLogSpace
(
DataBaseName VARCHAR(100) ,
LogSize NUMERIC(18, 4) ,
LOgPercentage NUMERIC(18, 4) ,
Status INT
);
INSERT INTO #TempForLogSpace
EXEC ( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS'
);
SELECT smf.name AS 'Logical Log File Name' ,
CASE WHEN smf.is_percent_growth = 0
THEN 'file is set to a fixed value and is considered best practice'
WHEN smf.is_percent_growth = 1
THEN 'See modify_log_file_statement---->'
END AS log_growth_setting ,
CASE WHEN smf.is_percent_growth = 0 THEN '--no alter DB statement needed'
WHEN smf.is_percent_growth = 1
THEN 'ALTER DATABASE '+sd.name + CHAR(13)
+ 'MODIFY FILE ' + CHAR(13) + '(NAME = ''' + smf.name
+ ''',' + CHAR(13) + 'FILEGROWTH = 512MB)'
END AS Modify_Log_File_statement ,
t.LogSize / 1024 AS 'Log Size (GB)' ,
t.LOgPercentage AS 'Log Space Used(%)' ,
CASE WHEN sd.log_reuse_wait_desc = 'LOG_BACKUP'
THEN 'please backup the log---->'
WHEN sd.log_reuse_wait_desc = 'ACTIVE_TRANSACTION'
THEN 'you have an active transaction..it must complete or be killed before log will clear'
ELSE 'no log backup required'
END AS Log_Backup_Action ,
sd.log_reuse_wait_desc ,
'Backup log '+sd.name +' to disk = ''' + ISNULL(@backuploc,'c:\')
+sd.name +'_Tlog_backup.trn'' with stats = 5' AS Backup_Statement ,
'Use ' + t.DataBaseName + ';' + CHAR(13) + 'DBCC SHRINKFILE ('
+ smf.name + ' , 1024)' AS SHRINKFILE_statement ,
'Alter Database'+sd.name+' SET Recovery Simple' AS Change_Recovery_Model_to_Clear_log ,
smf.physical_name AS Log_File_Location
FROM #TempForLogSpace AS t
INNER JOIN sys.databases AS sd ON t.DataBaseName = sd.name
INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
WHERE smf.type_desc <> 'rows'
AND sd.database_id > 4;
PRINT 'this process will give you the statements to fix the log_file issue..'+CHAR(13)+CHAR(13)+
'if the log file is set to grow by a percent the script will provide you the statement to change to fixed value of 512MB..'+CHAR(13)+
'if log_growth_setting is "see modify_log_file_statement---->" then copy the Modify_Log_File_Statement and execute..'+CHAR(13)+CHAR(13)+
'Log_Backup_Action column will advise you on if a backup is required..'+CHAR(13)+
'if Log_Backup_Action returns "please backup the log---->" then copy the backup_statement and execute'+CHAR(13)+CHAR(13)+
'After you take a backup rerun this process and see if the Log Space Used(%) is now at an acceptable level, it should be under 5% if there are no active transactions.. '+CHAR(13)+
'If the Log Space Used(%) is at an acceptable level you can now execute the SHRINKFILE_Statement. Depending on how large the file is, this could take some time.. '+CHAR(13)+CHAR(13)+
'If you find this overwhelming or overly confusing you can execute the Change_Recovery_Model_to_Clear_log statement and then the SHRINKFILE_Statement..'
USE master
GO
Log_Desc column in sys.Databases
When troubleshooting Transaction Log issues, one of the first places to look is the LOG_DESC column in sys.databases.
Second place to look is DBCC sqlperf('logspace') to tell you the current state of the space used inside the transaction log.
I ran into an issue with a value in LOG_DESC column that baffled me. It was telling me the log reuse was waiting on REPLICATION. My issue was that this 2012 instance doesn't have replication set up. Took me a while, but I found this website and it was a great help.
So my issue was that CDC(change data capture) is enabled and used on this server. CDC uses the TLog much like replication thus the wait type of Replication. First, check that the CDC capture and CDC CleanUp jobs are enabled first. Running the sp_repldone should used in an emergency as it will drop transactions queued up for the CDC process.
USE [Database_Name]
GO
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1
It seems to kind of act like a check point to the replication command. ( Any SQL ninjas want to correct me on this please chime in)
In addition to fix the problem CDC Capture and CDC Clean needs to run to begin to free up the log
EXEC sys.sp_cdc_add_job ‘capture’
GO
EXEC sys.sp_cdc_add_job ‘cleanup’
GO
so an update to this problem. I ran into it again and none of the above worked. So I found an article posted by Paul Randal on this issue.
I had to run this SP to release the log.
sp_removedbreplication
Second place to look is DBCC sqlperf('logspace') to tell you the current state of the space used inside the transaction log.
I ran into an issue with a value in LOG_DESC column that baffled me. It was telling me the log reuse was waiting on REPLICATION. My issue was that this 2012 instance doesn't have replication set up. Took me a while, but I found this website and it was a great help.
So my issue was that CDC(change data capture) is enabled and used on this server. CDC uses the TLog much like replication thus the wait type of Replication. First, check that the CDC capture and CDC CleanUp jobs are enabled first. Running the sp_repldone should used in an emergency as it will drop transactions queued up for the CDC process.
USE [Database_Name]
GO
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1
It seems to kind of act like a check point to the replication command. ( Any SQL ninjas want to correct me on this please chime in)
In addition to fix the problem CDC Capture and CDC Clean needs to run to begin to free up the log
EXEC sys.sp_cdc_add_job ‘capture’
GO
EXEC sys.sp_cdc_add_job ‘cleanup’
GO
so an update to this problem. I ran into it again and none of the above worked. So I found an article posted by Paul Randal on this issue.
I had to run this SP to release the log.
sp_removedbreplication
Find total size of all Log files on an Instance |
|
Needed to do some capacity planning for the log files regarding a migration to new hardware. Here is a little query to help out.
IF OBJECT_ID('tempdb..#TempForLogSpace') IS NOT NULL
BEGIN
DROP TABLE #TempForLogSpace
END
CREATE TABLE #TempForLogSpace
(
DataBaseName VARCHAR(100) ,
LogSize NUMERIC(18, 4) ,
LOgPercentage NUMERIC(18, 4) ,
Status INT
)
INSERT INTO #TempForLogSpace
EXEC ( 'DBCC sqlperf(logspace)'
)
SELECT SUM(logsize/1024.0) AS TotalLogSizeinGBs,@@SERVERNAME AS Servername FROM #TempForLogSpace
IF OBJECT_ID('tempdb..#TempForLogSpace') IS NOT NULL
BEGIN
DROP TABLE #TempForLogSpace
END
CREATE TABLE #TempForLogSpace
(
DataBaseName VARCHAR(100) ,
LogSize NUMERIC(18, 4) ,
LOgPercentage NUMERIC(18, 4) ,
Status INT
)
INSERT INTO #TempForLogSpace
EXEC ( 'DBCC sqlperf(logspace)'
)
SELECT SUM(logsize/1024.0) AS TotalLogSizeinGBs,@@SERVERNAME AS Servername FROM #TempForLogSpace