3

I would like to be able to look back and see what the used space was in transaction log over a given period. Not interested in growths or file size as they don't tell me how much space is actually being used.

I am switching from simple model to full recovery model and need an idea of how large the transaction log will get.

2
  • Why do you want an estimate? Switch it into full mode and find out. Just make sure you take regular backups of the transaction log using SQL Agent. If you're worried it's about to fill up the log disk, swap it back into simple mode and run the checkpoint command to free the log space.
    – Jhunter1
    Commented Feb 28, 2020 at 21:57
  • Thanks the reason for the estimate is because we are moving to brand new servers with full recovery therefore need an idea of disk space requirements however it looks like I will have to turn on full mode on the existing system first as you suggested to get a clear idea Commented Feb 29, 2020 at 16:39

2 Answers 2

5

Nothing is logged by default, generally speaking. So, unless you have something keeping track of the amount of log generated, you are out of luck.

Ordinarily, we would recommend you to check the size of your log backups, but as I understand it, you don't have those and the purpose here is to determine how much space you would need for those. Catch 22. :-)

Even with some active monitoring, you would have to check this pretty frequently, since you never know when checkpoint kicks in and "truncate the log". There's a perfmon counter you can use for this: "Log File(s) Used Size (KB)". And you can grab the value from sys.dm_os_performance_counters. I.e., pretty easy to create a job that uses a SELECT to check the used space in the tlog for selected databases, store it in a table and execute that job every minute, or whatever frequency you find suitable.

0

history of the transaction log used space.

there are a few things to consider - if the database is involved in replication, the portion of the transaction in the log will be marked are reusable only after it has been replicated - talking about transactional replication.

one way to check what is preventing the transaction log from reusing its

select log_reuse_wait_desc, * from sys.databases
where log_reuse_wait_desc != 'NOTHING'

enter image description here

another thing is the volume of transactions and how long the transactions are. this is a complete whole topic that basically you try to work around and keep transactions short, if possible with not long running queries that cause locks and blocks.

now one thing you could do it is through extended events

-- Extended Events Session to track what is growing my transaction log
-- marcelo miorelli
-- 02-Nov-2017


SELECT @@SERVERNAME



CREATE EVENT SESSION [DB Size Tracking] ON SERVER 
ADD EVENT sqlserver.database_file_size_change(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\DB Size Tracking.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

another way to check what sort of space of transaction log is being used and what is free is using dbcc commands:

dbcc sqlperf

dbcc inputbuffer

dbcc opentran

--===================================================
-- here on this scrip
-- I have 3 ways of monitoring transaction log growth
-- 1 - dbcc sqlperf (logspace)
-- 2 - sys.dm_db_log_space_usage
-- 3 - sys.dm_os_performance_counters
-- use and improve it for your needs
-- 14-nov-2023 marcelo miorelli 
--===================================================

if object_id('tempdb..#TempForLogSpace','U') is not null
    DROP TABLE #TempForLogSpace

CREATE TABLE #TempForLogSpace
(
DBName nvarchar(108),
LogSize real,
LogSpaceUsed real,
Status int
)
declare @sql_command varchar(max)
SELECT @sql_command = 'dbcc sqlperf (logspace)' 
INSERT INTO #TempForLogSpace 
EXEC (@sql_command)

select t.DBName
,[Log Size (GB)]=CONVERT(decimal(12,2),(t.LogSize)/1024.00)  
,[% Log Space Used]=CONVERT(decimal(12,2),t.LogSpaceUsed)  
,[Free space (GB)]= CONVERT(decimal(12,2),(t.LogSize - (t.LogSpaceUsed*t.LogSize/100.00))/1024.00)  
,[Free space (MB)]= CONVERT(decimal(12,2),(t.LogSize - (t.LogSpaceUsed*t.LogSize/100.00)))  
from #TempForLogSpace t
where dbname = 'Cobra_Archive'

select [DBName]=db_name(db_id())
      ,[Log Size (GB)]=CONVERT(decimal(12,2),total_log_size_in_bytes/1024.00/1024.00/1024.00)
      ,[% Log Space Used]=CONVERT(decimal(12,2),used_log_space_in_percent)
      ,[Free space (GB)]= CONVERT(decimal(12,2),(total_log_size_in_bytes/1024.00/1024.00 - (used_log_space_in_percent*total_log_size_in_bytes/1024.00/1024.00/100.00))/1024.00)  
      ,[Free space (MB)]= CONVERT(decimal(12,2),(total_log_size_in_bytes/1024.00/1024.00 - (used_log_space_in_percent*total_log_size_in_bytes/1024.00/1024.00/100.00)))  
from sys.dm_db_log_space_usage 


DECLARE @sample_time_utc DATETIME2(3) = GETUTCDATE();
if object_id('tempdb..#perf','U') is not null
    DROP TABLE #perf
    SELECT
        [DBName]=MIN(r.instance_name)
       ,[Log Size (GB)]=SUM(CASE WHEN r.counter_name = 'Log File(s) Size (KB)'        THEN CONVERT(decimal(12,2),r.cntr_value/1024.00) ELSE 0.00 END)
       ,[% Log Space Used]=SUM(CASE WHEN r.counter_name = 'Percent Log Used'          THEN CONVERT(decimal(12,2),r.cntr_value) ELSE 0.00 END)
       into #Perf
    FROM sys.dm_os_performance_counters r
    WHERE r.instance_name = 'Cobra_archive'
      AND (r.object_name LIKE 'SQLServer:Databases%' AND r.counter_name IN ('Log File(s) Size (KB)', 'Log File(s) Used Size (KB)', 'Percent Log Used'))

    SELECT r.DBName
    ,[Log Size (GB)]=CONVERT(decimal(12,2),r.[Log Size (GB)]/1024.00)
    ,r.[% Log Space Used]
    ,[Free space (GB)]= CONVERT(decimal(12,2),(r.[Log Size (GB)] - (r.[% Log Space Used]*r.[Log Size (GB)]/100.00))/1024.00)  
    ,[Free space (MB)]= CONVERT(decimal(12,2),(r.[Log Size (GB)] - (r.[% Log Space Used]*r.[Log Size (GB)]/100.00)))  
    FROM #perf r

   

enter image description here

On the above pictures I used 3 different methods to get the log file size with the same results - one of them is sys.dm_db_log_space_usage

The last one shows the sys.dm_os_performance_counters which seems to be the least accurate.

--Another is dbcc inputbuffer :-
if object_id('tempdb..#dbc','U') is not null
    DROP TABLE #dbc

create table #dbc(c1 varchar(15), c2 int, c3 varchar(255))

insert #dbc EXEC('dbcc inputbuffer(7)')

select * from #dbc

![enter image description here

to get historic data you need to start saving monitoring info into a table so that you can refer back to it as required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.