Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
What does the log_reuse_wait_desc in sys.databases say? Read Factors That Can Delay Log Truncation. –  Remus Rusanu Jul 12 '13 at 10:48
    
Is the Recovery Model set to FULL? Transaction log backups do not automatically shrink or truncate the transaction log, so it might keep growing. Check out this article. –  Josien Jul 12 '13 at 10:49
add comment

migrated from stackoverflow.com Jul 12 '13 at 9:22

This question came from our site for professional and enthusiast programmers.

3 Answers

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

share|improve this answer
    
Thanks Adam. So, you're saying that nightly full backups and transaction log shipping every 15 mins should result in a truncating log file? If not, how can I back up the log files and not break the LSN? I ran the DBCC OPENTRAN command on the DB in question and it returned stating no active open transactions. I also checked the log shipping was operational - on the secondary server, the windows log seemed happy stating a log was restored less than 10 mins ago. When you say to shrink the database, presumably you mean the logfile, and not the actual ndf file, etc? –  Loic Jul 12 '13 at 14:17
    
Yes, log shipping should be truncating your log. Are you using home growth log shipping or did you use the wizard in SSMS? The reason I ask is that you could also put a no_truncate in your backup log statement, which prevents log truncation. -- And yes, I should have been more explicit, shrink the log file, is correct. –  Adam Haines Jul 12 '13 at 14:24
    
I used the winzrd in SSMS to set up TLS. Last log backup according tot he DB properties was 2 mins ago... –  Loic Jul 12 '13 at 14:46
1  
Run dbcc loginfo() to see what portions of the log are considered active.... status 2 = active, 0 =inactive. The log backup should mark parts of the log file inactive and those should be resuable, but if the log is growing, it indicates they are not available for reuse. Can you try increasing the backup frequency to see if that helps.. How much free space is in the log? –  Adam Haines Jul 12 '13 at 15:08
add comment

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.

share|improve this answer
    
Thanks Kenneth - seems I've got the wrong end of the stick then. I thought a full backup + transaction log shipping truncated a log file, but it would appear to not be the case! So, I'll set up regular log backups, take a full backup of the database, flip it over to 'Simple' recovery model, reduce the log file size (NOT the database file size), flip it back to 'Full' recovery then see how the log file size goes, going forward. Thanks for the help! –  Loic Jul 12 '13 at 13:39
1  
I would have thought the log shipping process is doing log backups? If you take a look at the properties of your database does it show a recent log backup? Right click on database name and select Properties. Then look at Last Database log backup. –  Kenneth Fisher Jul 12 '13 at 14:30
    
last log backup was a few minutes ago. The transaction log shipping appears to be working absolutely fine, with notification of restored transaction files going into the secondary database in the Windows event viewer on the secondry server. –  Loic Jul 12 '13 at 14:50
    
Then don't add additional transaction backups. @AdamHaines is correct. It will mess up your log shipping. If your log file is showing mostly empty space then my second thought becomes more likely. You have a process that is using up the space. Shrink your log file to a reasonable size and see if it grows back. If it does you have a process that is causing it. –  Kenneth Fisher Jul 12 '13 at 15:13
add comment

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?

share|improve this answer
    
Hi all, thanks for the responses. Answers to your questions: Remus: log_reuse_wait_desc for that database says 'LOG_BACKUP' which according to the article you linked, doesn't affect log truncation. Josien: the database is certainly set to full recovery. Craig: If I run your query repeatedly, I see the odd transaction being processed, but it often comes back blank, so I don't think there are any transactions that are stuck. –  Loic Jul 12 '13 at 12:27
    
Loic, this is because the query is only configured to show running queries. If you remove "AND er.status = 'running'" from the where clause, you will probably find the session. Another column to check is the start_time to see if any querieis have been active for a very long time (several days) –  Craig Efrein Jul 12 '13 at 13:05
    
Thanks Craig. Looking at it, I just see sleeping or dormant sessions. The 'start_time' is null for every session. What am I looking for to identify an open transaction? Edit: I also ran 'DBCC OPENTRAN' and it returned 'no active open transactions' –  Loic Jul 12 '13 at 14:06
    
Updated answer, awaiting your reply –  Craig Efrein Jul 12 '13 at 14:26
    
Thanks Craig - interestingly it's stating 78689.41 MB (99%) free space. Perhaps setting up the transaction log backup was what was needed, though Adam, above, said this was a no-no when using transaction log shipping. –  Loic Jul 12 '13 at 14:45
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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