Full Backup Not Truncating Logs SQL Simple Recovery

[squiggie]

I have a backup exec server running BE 2014 backing up databases on a SQL 2008 server. There are a few databases that have large transaction logs on them that aren't shrinking. All of these databases are in simple recovery mode. When I run dbcc loginfo on the databases, all of them have VLF's that have a status of 2 meaning they can't be shrunk until a backup has been done on them. 

In Backup Exec, I've tried configuring a full database backup as well as a full followed by an incremental backing up only the log and truncating it. Each one of these doesn't truncate the log nor change the Status = 0 which would allow the dbcc shrinkfile to run and shrink the log.

Now as I understand it, databases in simple recovery shouldn't need an incremental backup that truncates the log; a full should do that. IS that correct? Also, if not, how would I setup a backup that truncates the log as the goal of all this is to keep the growing transaction logs under control.

In order to shrink the log file, I've had to run the following manual queries. 

backup log dbname to disk = 'path\to\backup' (sometimes run this a few times to get all the status = 0)
dbcc shrinkfile dbname

[pkh]

If your database is really using the simple recovery mode, I am surprised that you don't get an error message when you do a log backup.  BE does not allow log backups of databases with simple recovery mode.  It only allows log backups of databases with full recovery mode.  After a log backup, you can then use DBCC Shrinkfile to shrink the log.

I would suggest that you either do a log backup using SQL Studio and try to shrink the log (I don't think this would be successful for simple recovery mode databases) or check with Microsoft.

[VJware]

Few key points -

DBs with Simple Recovery Model does not support and does not require Transaction Log backups.

Log truncation for such DBs happen automatically outside user control.

Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. (Verbatim from TECH33654)

Lastly, if the Simple Recovery Model DBs have large Transaction Logs, troubleshoot if Checkpoints are occuring properly or not and if they are, then is the truncation getting delayed or not occuring. Best to involve MS's support on this.

[squiggie]

Thanks for the feedback. I have a couple of followup questions.

1. I'm positive that these databases are in simple mode. Does the log truncation happen automatically even if Auto Shrink is set to false? If so, what triggers this and is there a setting that could possible turn this off?

2. I understand that log truncation does not reduce the physical size. However still yet, I can run a 'backup log' query a few times to mark the VLF's as 0 and then it allows me to do a 'dbcc shrinkfile' to reduce the size of the physical log file. This is the only way I've been able to shrink these files.

3. I'll look into checkpoints. I'm not too familiar with them so any additional help on how they would would be appreciated.

[Colin_Weaver]

For the differences in SImple mode vs the other modes you should look at aricles on the Microsoft Website at a basic level Simple mode is a from of Circular logging that does not need log trucation by the backup product as SQL itself ensures that changes are rolled directly into the main database irrespective of when backups run.

BTW be careful if you have more than one database in a SQL instance I think it might be possible to have a mixture of modes and you therefore might have a database that does need log truncation.