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.

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate automagically at some time point? Or do I have to truncate/shrink it manually?

share|improve this question
add comment

3 Answers

up vote 13 down vote accepted

It will truncate automatically but that is very different to shrink. Truncation reclaims log space for re-use, shrinking physically reduces the file size to release space back to the OS. If your log has grown to its current size its likely that it will grow again if you shrink it.

I'd suggest getting a handle on what typical and maximum log usage is for your system. The query below (not mine, boosted from Glen Berrys DMV scripts) could be run manually or you could capture the output to a table via an agent job. If you log it to a table for a week or so you'll get a picture of typical usage and more importantly, when a process is causing the log to grow beyond what you expect.

SELECT 
     db.[name] AS [Database Name]
   , db.recovery_model_desc AS [Recovery Model]
   , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
   , ls.cntr_value AS [Log Size (KB)]
   , lu.cntr_value AS [Log Used (KB)]
   , CAST(
        CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) 
        AS DECIMAL(18,2)
     ) * 100 AS [Log Used %]
   , db.[compatibility_level] AS [DB Compatibility Level]
   , db.page_verify_option_desc AS [Page Verify Option]
   , db.is_auto_create_stats_on, db.is_auto_update_stats_on
   , db.is_auto_update_stats_async_on, db.is_parameterization_forced
   , db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on
FROM sys.databases AS db
   INNER JOIN sys.dm_os_performance_counters AS lu 
     ON db.name = lu.instance_name
   INNER JOIN sys.dm_os_performance_counters AS ls 
     ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
   AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
   AND ls.cntr_value > 0 
OPTION (RECOMPILE);

Transaction Log Truncation describes both the when and why log truncation occurs.

If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.

Factors That Can Delay Log Truncation is a useful reference for understanding why your log may fail to truncate and therefore grow larger than expected.

share|improve this answer
    
gbn's answer contradicts yours. Could you add a reference to backup "It will truncate automatically"? –  Nick Chammas Oct 26 '11 at 20:23
3  
the contradiction is a result of what it means to "truncate". for some, that means "cleared", for others it means "make smaller", and it is and endless source of confusion for many. –  SQLRockstar Oct 26 '11 at 21:27
1  
@Nick Have added a couple of references. –  Mark Storey-Smith Oct 26 '11 at 22:18
add comment

No and no

  • it won't shrink or truncate (in the physical LDF sense, it will do logically)
  • it needs to be the size it is so you don't shrink it

If you shrink it, it will grow again and you'll have a fragmented file

share|improve this answer
    
+1 if it was not fragmented before. :) –  garik Jul 27 '11 at 21:41
    
Can you clarify what you mean by truncate? (See comments on Mark's answer.) –  Nick Chammas Oct 26 '11 at 22:39
    
@Nick: clear, as in log entries. "truncate log" is not the same as "truncate table". –  gbn Oct 27 '11 at 4:35
add comment

As previously mentioned, no it will not automatically shrink itself. It will clean up some garbage however.

The reason being is that in the full recovery model you are telling SQL that you want to do tlog backups for point in time recovery, thus it keeps a record of all transactions made against a database.

Since you are telling it you want point in time recovery you need to do full backups and tlog backups. As you complete your tlog backups it will flush the contents of the log (besides the tail end) and start over.

It may help if you think of these files as containers.

My suggestion is that if the tlogs have become large and unmangable, cut a full backup. Switch to the SIMPLE recover model and SHRINK the tlog file. Switch back to the full recovery model and preform fragmentation maintenance. Like others have posted this is not the best of practices and will lead to high levels of fragmentation.

Plan and begin a backup regime after that.

share|improve this answer
    
Could you clarify what is meant by "Switch back to the full recovery model and perform fragmentation maintenance"? –  Mark Storey-Smith Oct 31 '11 at 0:42
    
Index rebuild/reorganize operations and disk level defragmentation. Im not sure of any other defrag options –  RMacy Nov 4 '11 at 1:08
    
my comment was intended to be a prompt for you to edit your answer. As it stands, you are suggesting that after switching back to FULL you should run index rebuild/reorganise which will of course cause the log to grow again. I don't see how this fits the question, nor why you see it as part of log maintenance. –  Mark Storey-Smith Nov 4 '11 at 6:29
    
It isn't apart of log maintenance. How do you maintain your T Logs? You back them up. They are containers that grow when they get close to capacity. I was instructing the OP on one of the ways they could recover from improper log management leading to large drive usage. –  RMacy Nov 8 '11 at 1:43
add 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.