Sql-server – Transaction Log autogrowth

sql serversql server 2014

In my recent studies about Auto Growth, I took a look at one of our DB's at work and see the Transaction log has many Auto Growth events. My understanding is that the Auto Growth event is a blocking event and should be avoided. So my goal is to stop this recurring Auto Growth. Is this happening because the reserved Log space is too small and the transactions fill it up immediately triggering the Auto Growth? Also, In this DB, they are taking TLog backups every hour on the hour.
Here is a pic from the Disk Usage Report.
Thanks!

Best Answer

I think you'll find a good starting point here - http://blog.sqlxdetails.com/set-transaction-log-initial-size/

To summarize from the link:

Set your transaction log large enough so it will not grow during normal operation. All update activity stops in the database when the logs grows, so you want to minimize log growths.
For in initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS. You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process. While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
Generally speaking, shrinking the log is discouraged. At some point, there was activity that caused it to grow to that size (index rebuild, etc.). There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size

Related Solutions

Sql-server – Autogrowth of database and log file

If we set autgrowth for log file as restricted then logs size will increase up to specified limit and then it will start discarding old logs and new logs will be there in logs.

This is wrong. If it has to grow it will grow until the max size and after which further growth request will result in failure. You are confusing things with log truncation, which is the process which allow log physical file to be reused (so it doesn't have to grow). I have an article explaining how log grows and what is the role of truncation in this, see How to Shrink SQL Server log.

SQL Server 2008 R2 – Unexplained Log Ship Slow Down

This is bigger than a comment but something to test first and then implement :

You are logshipping 800+ databases. Thats a large amount of databases that you logship every 15 mins.

You should offload some databases to another server. IMHO 800 databases on a single server is a lot!

We had similar problem with logshipping when we logshipped 200+ databases from NY to LD region.

What we did is as below :

There was blocking writing to msdb.dbo.sysjobhistory with (TABLOCKX). The TABLOCK hint means that access to the sysjobhistory is always serialized. And since you have lot of jobs running every 15mins, there will be contention (blocking).
We implemented trace flag TF – 1236. It will introduce Database lock partitioning. Partitioning the DATABASE lock keeps the depth of the lock list manageable in each local partition. This significantly optimizes the access path that is used to obtain a DATABASE lock.

create indexes on sysjobhistory and log_shipping_monitor_history_detail tables as below :

use msdb
    go

    create nonclustered index [nc_DBA_sysjobhistory] on dbo.sysjobhistory (
            ,[job_id] 
            ,[step_id]     
            ,[run_date]
            ) 
    include (
            [instance_id]
            ,[step_name]
            ,[sql_message_id]
            ,[sql_severity]
            ,[message]
            ,[run_status]
            ,[run_time]
            ,[run_duration]
            ,[retries_attempted]
            ,[server]
            )
    go

    use [msdb]
    go
    ----- this will help sys.sp_MSprocesslogshippingretentioncleanup proc (delete from msdb.dbo.log_shipping_monitor_history_detail).. that does the cleanup of logshipping.

    create nonclustered index [nc_DBA_LogShipping_monitor_history_detail] on [dbo].[log_shipping_monitor_history_detail] (
            [agent_id] asc
            ,[agent_type] asc
            ,[log_time_utc] asc
            )
    go

Best Answer

Related Solutions

Sql-server – Autogrowth of database and log file

SQL Server 2008 R2 – Unexplained Log Ship Slow Down

Related Question