Sql-server – How to prevent SQL Log from being too full

backupsql-server-2008-r2

I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even though there are a lot of transactions, the actual data is pretty small.

Now the problem I have is that when the log reaches his maximum value, which is pretty big to being with, the database will not accept anymore transactions. I can't really put unrestricted growth since I do have a physical limit to respect.

My initial understanding was that once the log reaches full size , it will simply take the oldest transaction and delete them to make space for the new ones, which would be ok in our case. However, this does not seem to be the case here. I am no expert on SQL so I am at a lost here.

I understand this log is used for backups, but so far we don't have plans to implement backup because we are still on testing phases. Also we won't be the one performing it, so we have to wait for now.

Thanks

Best Answer

A couple of things you can do.

Your database is probably in full recovery mode. This is why the log file does not get re-used. Now if you DON'T need transaction log backups you can set the recovery mode to simple. Your log file will now re-use itself in simple mode. If you DO need transactional log backup i.e. point in time recovery then you will need to actually DO transaction log backups to re-use the log file.

Please be aware that this setting has a MAJOR impact on how you can perform disaster recovery and you need to make sure that the setting matches your disaster recovery needs. If you database is so busy then transactional backups are probably better than simple recovery mode.

Without transaction log backups you will NOT be able to do point in time recovery. Thus you will lose A LOT OF DATA in the event of a disaster.

Go read about transaction logs and recovery modes here: http://msdn.microsoft.com/en-us/library/ms189275.aspx

Now there is something else you need to keep in mind. Virtual Log File fragmentation. It is going to be something very important to understand and master. Kimberly L Tripp has a very very usefull blog entry about this. You can read all about this very helpful topic here: http://www.sqlskills.com/blogs/kimberly/post/transaction-log-vlfs-too-many-or-too-few.aspx

Keep in mind 50-100 transactions a second is not a lot. Also a BIG transaction log file is not always a bad one. There is more to log files than just logging transactions. Go read about it. It is a topic worth mastering.

Related Solutions

Sql-server – How to pinpoint root cause of excessive log file growth

Best thing to do is to collect what caused the log file to grow by setting up an alert.

enter image description here

You can use sp_whoisactive to run and log into the table (as DennisT suggested) or dmv's like sys.dm_tran_active_transactions and sys.dm_tran_database_transactions

--  Find Log space usage
-- http://stackoverflow.com/a/5085179/1387418
select
    SessionTrans.session_id as [SPID],
    enlist_count as [Active Requests],
    ActiveTrans.transaction_id as [ID],
    ActiveTrans.name as [Name],
    ActiveTrans.transaction_begin_time as [Start Time],
    case transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        when 4 then 'Distributed'
        else 'Unknown - ' + convert(varchar(20), transaction_type)
    end as [Transaction Type],
    case transaction_state
        when 0 then 'Uninitialized'
        when 1 then 'Not Yet Started'
        when 2 then 'Active'
        when 3 then 'Ended (Read-Only)'
        when 4 then 'Committing'
        when 5 then 'Prepared'
        when 6 then 'Committed'
        when 7 then 'Rolling Back'
        when 8 then 'Rolled Back'
        else 'Unknown - ' + convert(varchar(20), transaction_state)
    end as 'State',
    case dtc_state
        when 0 then NULL
        when 1 then 'Active'
        when 2 then 'Prepared'
        when 3 then 'Committed'
        when 4 then 'Aborted'
        when 5 then 'Recovered'
        else 'Unknown - ' + convert(varchar(20), dtc_state)
    end as 'Distributed State',
    DB.Name as 'Database',
    database_transaction_begin_time as [DB Begin Time],
    case database_transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        else 'Unknown - ' + convert(varchar(20), database_transaction_type)
    end as 'DB Type',
    case database_transaction_state
        when 1 then 'Uninitialized'
        when 3 then 'No Log Records'
        when 4 then 'Log Records'
        when 5 then 'Prepared'
        when 10 then 'Committed'
        when 11 then 'Rolled Back'
        when 12 then 'Committing'
        else 'Unknown - ' + convert(varchar(20), database_transaction_state)
    end as 'DB State',
    database_transaction_log_record_count as [Log Records],
    database_transaction_log_bytes_used / 1024 as [Log KB Used],
    database_transaction_log_bytes_reserved / 1024 as [Log KB Reserved],
    database_transaction_log_bytes_used_system / 1024 as [Log KB Used (System)],
    database_transaction_log_bytes_reserved_system / 1024 as [Log KB Reserved (System)],
    database_transaction_replicate_record_count as [Replication Records],
    command as [Command Type],
    total_elapsed_time as [Elapsed Time],
    cpu_time as [CPU Time],
    wait_type as [Wait Type],
    wait_time as [Wait Time],
    wait_resource as [Wait Resource],
    reads as [Reads],
    logical_reads as [Logical Reads],
    writes as [Writes],
    SessionTrans.open_transaction_count as [Open Transactions],
    open_resultset_count as [Open Result Sets],
    row_count as [Rows Returned],
    nest_level as [Nest Level],
    granted_query_memory as [Query Memory],
    SUBSTRING(SQLText.text,ExecReqs.statement_start_offset/2,(CASE WHEN ExecReqs.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), SQLText.text)) * 2 ELSE ExecReqs.statement_end_offset end - ExecReqs.statement_start_offset)/2) AS query_text
from
    sys.dm_tran_active_transactions ActiveTrans (nolock)
    inner join sys.dm_tran_database_transactions DBTrans (nolock)
        on DBTrans.transaction_id = ActiveTrans.transaction_id
    inner join sys.databases DB (nolock)
        on DB.database_id = DBTrans.database_id
    left join sys.dm_tran_session_transactions SessionTrans (nolock)
        on SessionTrans.transaction_id = ActiveTrans.transaction_id
    left join sys.dm_exec_requests ExecReqs (nolock)
        on ExecReqs.session_id = SessionTrans.session_id
        and ExecReqs.transaction_id = SessionTrans.transaction_id
    outer apply sys.dm_exec_sql_text(ExecReqs.sql_handle) AS SQLText
where SessionTrans.session_id is not null -- comment this out to see SQL Server internal processes

More reference :

Sql-server – Switching from SIMPLE to FULL recovery mode implications

There are lot of things I would like to say

What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.

As far as I know if you change recovery model of database during maintenance window time or when load is relatively less there wont be any problem. It wont create a situation.

I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).

You can take differential backup also perhaps this would be very helpful if you have big database. anything that links LSN chains would do.See below link it has good information about switching between recovery models

http://msdn.microsoft.com/en-gb/library/ms189272.aspx

My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.

Stop there, this is not correct approach just because you did a full backup and log backup please don't be in idea that you can safely remove old log files and full backup. Do you have a plan to test old backup by restoring to actually see that in case of disaster this backup files would work. Remember only successful restore guarantees that your backup is totally consistent. Do you have a option to check backup integrity.If not please include it into your backup plan. At least keep 4 day old backup files( this is what I do on local disk) before if you delete backups This strategy is upto you keep backups for time where you are sure they wont be useless if disaster strikes. Sometimes business want only specific data change to which was made couple of days. I also have my database backup up on tape and that tape is stored for 6 months.

(maybe switch to BULK_LOGGED for that time period as to not explode the log file?)

Changing recovery model to bulk logged and doing bulk logged operation would make you loose point in time(PIT) recovery so if you are concerned about PIT recovery dont do that. Instead rebuild index through intelligent script which only rebuild fragmented index like one Is Ola Hallengren index rebuild solution please note that if Index is rebuild with full scan for that index stats is already updated with rebuild process.

If you do heavy DML please break it into batches so as not to explode log files.

Best Answer

Related Solutions

Sql-server – How to pinpoint root cause of excessive log file growth

Sql-server – Switching from SIMPLE to FULL recovery mode implications

Related Question