SQL Server 2008 - performing nightly full backups & transaction log shipping, but log file keeps growing

Question

Rather perplexed with my SQL server Log file. With identical primary & secondary servers, the production server on live sends the log files to the secondary every 15 minutes. Also, every evening, a full backup (using the maintenance plan wizard) performs a full backup to a file location on the secondary server.

However, the log file seems to continually steadily grow. The database is 43Gb in size, and the log file has steadily increased to nearly 81Gb now. There are only about 80 users using the database.

On top of the full backup, I added a transaction log backup just to see if this made a difference, but still the log file is expanding. There's a weekly re-indexing job that runs on a Saturday night.

Database is on SQL Server 2008R2 (was migrated from a 2005 server a few months ago). It's in 2008 compatibility mode now, Full recovery model.

Any help greatly appreciated!

Answer 1

On top of the full backup, I added a transaction log backup just to see if this made a difference

This is a big no no. By doing a manual transaction log backup, you can break the LSN of the log shipping secondary and cause the restores to fail, unless you used the copy_only option, which doesnt allow for truncation anyway.

There are only a few things that can cause a log not to truncate/clear, an active transaction, the recovery model set to full w/ no t-log backups, or a feature that is not working properly (mirroring, replication etc...)

You can check for open transactions by running DBCC OPENTRAN in the context of the said database. Also, I know it may seem like a waste, but I would also confirm that your log backups are actually succeeding. It could be a situation where the backup drive is out of space and the backups are failing.

Edit: I forgot to add, when your log does truncate, you will want to shrink your log file as the number of VLFs will probably quite large and could degrade performance. You can use dbcc shrinkfile

Answer 2

Your comment On top of the full backup, I added a transaction log backup has me a bit worried. You are doing regular transaction log backups correct? You need to take regular transaction log backups when in FULL or BULK LOGGED recovery mode in order to clear the transaction log for reuse.

I did also notice that in BOL for sys.databases it says under the log_reuse_wait_desc column that if you are seeing LOG_BACKUP that it may take 2 backups to clear. Note this would be transaction log backups, not full backups.

All that being said it is also possible that you have some process that is continuously growing larger (say a cross join on two big tables) as your data grows larger. This would also cause your transaction log to increase in size over time but I find this to be an unlikely scenario.

Answer 3

Have you checked to make sure there isn't an open transaction that's still running on the production server?

SELECT
es.session_id
,er.blocking_session_id
,er.start_time
,es.status
,es.login_name
,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name
,er.command
,es.reads
,es.writes
,es.cpu_time
,er.wait_type
,er.wait_time
,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID
AND er.status = 'running'
ORDER BY er.total_elapsed_time desc

As long as a transaction is open and modifying data, the LDF will continue to grow, even if you perform a transaction log backup.

Several options :

A. Rollback the transaction (cancel the query, from the client running it)

or

A. Kill the session with (KILL {SPID})

1. Then perform a transaction log backup

2. Shrink the log file with DBCC SHRINKFILE

Note : The Query listed above is a replacement for sp_who2 from Brent Ozar.

Update 7/12 16:20

In SSMS, connect to your instance,

1. Right click on your database
2. Select Tasks
3. Select Shrink
4. Select Files
5. Under File Type, select Log

Can you please send back the value in Available free space?