7

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.

  • >>>Backup_finish_date shows null for my logs<<<What does it mean? Where do you check it, in the backupset? There are rows with backup_start_date filled up and backup_finish_date set to null? – sepupic Dec 19 '19 at 7:30
  • If you need point-in-time recovery, you need to be doing log backups. If you don't need point-in-time recovery, then you shouldn't be in FULL recovery mode; switch to simple. – BradC Dec 19 '19 at 16:00
  • Thanks everyone! Clearly we should make changes to our backup strategy. But in order to to that I was trying to figure out exactly what was happening. The psudo-simple recovery model seems to have answered my question. Thanks again! – Bob Larsen Dec 21 '19 at 19:39
11

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.

| improve this answer | |
  • See new related: Is Pseudo-Simple SQL Server Recovery a real thing? – James Jenkins Dec 19 '19 at 15:33
  • 1
    Yes. This may explain it...Back in 2014 we used to switch from full-simple-(to shrink the transaction logs)- then back to full. We started taking copy only backups at some point and found that the transaction logs were being truncated. Psuedo simple. – Bob Larsen Dec 19 '19 at 19:45
  • 1
    @BobLarsen after looking at Psuedo simple more and seeing your other comments, I concur this is exactly what is happening to you. It might be cleaner to change to simple recovery and remove COPY ONLY from your backup. – James Jenkins Dec 20 '19 at 14:27
5

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.

| improve this answer | |
  • Thank you. I ran this query. There are 1900 rows. They are all backup_type = full. All but the first 4 back in 2014 are copy only. The physical_device_name is simply the drive and the .bak file. – Bob Larsen Dec 19 '19 at 16:35
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
| improve this answer | |
  • This is an easy way to see just the log backups for a single database. – James Jenkins Dec 19 '19 at 13:47
  • Thank you. I ran a similar script before the post. But ran this one to make sure. Nothing is returned. 0 rows. – Bob Larsen Dec 19 '19 at 16:22
  • 2
    So it seems right that the recovery model was changed from Simple to Full and a Full backup wasn't taken after that change. Take a full backup WITHOUT copy_only,. Also you can check when was the recovery model changed dba.stackexchange.com/questions/214888/… – Ramakant Dadhichi Dec 19 '19 at 16:32
  • Thank you. I'm going to run check to see when the recovery model changed. I don't think it is being changed nightly. But this will be a good test... – Bob Larsen Dec 19 '19 at 16:43
  • 2
    That's the catch. When you switch the recovery model, you need to take a full backup to restart the LSN chain. SQL Server is still treating the recovery model as 'Simple recovery '. Please go through this to understand the implications of switching recovery model and how to bring back the LSN chain.sqlskills.com/blogs/paul/… – Ramakant Dadhichi Dec 19 '19 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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