Sql-server – How to estimate rate of Trans Log usage

sql servertransaction-log

We have a moderately sized database (about 500GB) that we're going to switch from Simple to Full Recovery, with Trans Log backups every 15 minutes. Before we make the switch, I want to get an estimate of how much space those backups will take on average. (Actually, our storage folks want to know so they can carve out that space for me.) So how can we get that estimate?

Looking at how much log space is in use won't do it, since it's just using one area in the log while freeing others. So I'm guessing I would have to look instead at the rate at which the transaction log is being used. The plan is to monitor the VLF usage and track which VLFs are used and then freed over time, along with their size.

That's going to be a little challenging to set up, but it's doable. Before I go down that route, though, I'm hoping there's a better/easier way. Any ideas?

Best Answer

You didn't specify which SQL version you are on but you could use a Server Side trace or an Extended events session to capture the traffic that is sent to the SQL server (if you can live with the performance impact it might have) and replay those captured traces on a test environment to see what the daily growth would be for the day where you ran your traces.

See here how to replay a profiler trace or distributed replay for info on how to run an XEvents replay.

That would allow you to rerun the captured workload on a test system and see for real what is happening with your log file.

Related Solutions

Sql-server – Transaction log growing on a mirrored principal

Mirror, Synchronized / Restoring... is the typical state for the mirror instance.

You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:

SELECT name, LOG_REUSE_WAIT_DESC 
FROM sys.databases 
WHERE name='your_database_here';

This will show several possible choices, such as:

NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION shown in the query above. For instance, there may be a long-running transaction that is forcing the log files to not be re-used.

Sql-server – Transaction log full due to log_backup

This error occurs because the transaction log becomes full due to LOG_BACKUP. Therefore, you can’t perform any action on this database, and In this case,

the SQL Server Database Engine will raise a 9002 error.

To solve this issue, you have to do the following:

Take a Full database backup.
Shrink the log file to reduce the physical file size.
Create a LOG_BACKUP.
Create a LOG_BACKUP Maintenance Plan to take backup logs frequently.

Note: The Shrink operation effects on SQL Server Performance during executing shrink command. it also causes index fragmentation and can slow the performance of queries that search a range of the index.

So, it’s recommended before goes live you should prepare LOG_BACKUP Maintenance Plan to back up the log file frequently to avoid the shrink operation on Production.

For more details check The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

Hope this helps you

Best Answer

Related Solutions

Sql-server – Transaction log growing on a mirrored principal

Sql-server – Transaction log full due to log_backup

Related Question