SQL Server Truncates Transaction Logs with Copy Only Backups

Question

I have a database that is set to "Full" recovery mode. The database doesn't show that a backup of the transaction log has ever been made. backup_finish_date shows null for my logs.

I take backups nightly of the database Full (Copy Only). The backup appears to truncate the transaction log. The transaction log is about 40GB. Before the backup it is 75% full. When the backup runs, it is only 1% full. Using DBCC SQLPERF(logspace).

The sys.databases shows: recovery_model_desc FULL, log_reuse_wait 0, log_reuse_wait_desc NOTHING.

I have a copy of that database on a different server, same setup, but the transaction logs are NOT truncating. Just growing.

The sys.database on the copy shows FULL, 2, LOG_BACKUP. Which is what I would expect.

I understand the copy of my database is the way things should work. But would like to know why/how the logs are being truncated on my production environment.

No Mirroring, No Transaction Log Shipping.

Answer 1

I recommend you use the code in one of the two existing answers to validate that t-logs are not being taken. Depending on the access your users have, someone may be taking backups without your knowledge.

There was a comment and link by Denis Rubashkin talking about Pseudo-Simple SQL Server Recovery Model Essentially it says that if you are in full recovery and have never taken a full backup (or otherwise disrupted the LSN chain) , SQL knows there is no way to recover, and it does not save the t-logs.

But given your description:

the backup appears to truncate the transaction log. The transaction log is about 40GB. Before the backup it is 75% full. When the backup run, it is only 1% full.

I don't think either of the two possibilities above are the cause of you issue.

I suspect that your backup job sets the database to SIMPLE recovery at some point in the process, this would clear the t-logs. Then it sets the database back to FULL recovery. This scenario would create the symptoms you are seeing.

Look through the backup code, if you find that a change to SIMPLE and back to FULL is occurring, you will want to change something. Either leave the database in SIMPLE recovery all the time, or take regular t-log backups. Which choice you make is a business decision, dependent on your recovery objectives.

There is no good reason to be in full recovery if you are not taking t-log backups.

Edit I just noticed another clue in your question.

nightly of the database Full (Copy Only).

Why are you taking Full (Copy Only) backups? The only reason to do this is you are taking a single backup and want to preserve the backup chain when you are relying on differential backups for your your recovery. See Copy-Only Backups

This clue suggests that there are Full, Differential and t-log backups occuring that you are not aware of, it would suggest that whomever wrote your nightly backup job as copy only was aware of the differentials and wanted to preserve the backup chain. If this is the case, I would expect t-logs to be taken several times a day, the normal scenarios is Fulls once per week, Differentials the other 6 days a week, and t-logs regularly through the day.

Or it could mean that the person who put the change to SIMPLE and back to FULL just randomly added it because they did not know the reason or impact of copy only either.

Answer 2

My answer to How to Get Backups & Restores Start and Finish Times? contains a script that will retrieve the backup information of all database on a SQL Server instance.

--------------------------------------------------------------------------------- 
--      Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT  

/* Columns for retrieving information */

   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full'  
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],  
   -- msdb.dbo.backupmediafamily.device_type,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupmediafamily.logical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,   
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'

FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   INNER JOIN msdb.dbo.backupmediaset
   on msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id


/* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs    
-------------------------------------------------------------------------------*/
WHERE 1 = 1
AND msdb.dbo.backupset.database_name = 'YOUR_DATABASE'
ORDER BY  
          2 desc, -- backup start
          1,      -- database name
          3 desc  -- backup end

^{Replace YOUR_DATABASE with the name of your database.}

Run this script against your SQL Server instance. You can add additional WHERE conditions to limit to search for your database and other information.

Now if the msdb.dbo.backupmediafamily.physical_device_name column contains other information than the device you are using for your copy_only backup, then that is a sign that some other solution is performing backups.

In a production environment I would expect to see entries for an enterprise solution similar to 287899b2-d08e-40c3-a83d-677d898b6671 (which is a backup solution identifier for a virtual tape drive).

Some tools will place a comment in the msdb.dbo.backupset.description column which might provide a hint what is going on.

See what you can retrieve from your backup history.

Answer 3

Definitely there are log backups running. Full recovery mode would make your logs grow to a point there is no more room left in the disk. Please use below script to find the log backups for your database:

select a.database_name,a.backup_size,backup_start_date,backup_finish_date,b.physical_device_name,a.type from msdb..backupset a  
inner join msdb..backupmediafamily b on a.media_set_id = b.media_set_id
where a.database_name = 'YourDatabaseName' and a.type = 'L'
order by backup_start_date desc