SQL Server – Transaction Logs Truncated with Copy Only Backups

sql servertransaction-log

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.

Best Answer

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.

Related Solutions

SQL Server – Changing Column Data Type Fills Transaction Log

Try to set your log file to autogrow or temporarily add some extra log files which can handle such an operation

BUT

it may be simplier and faster to do something like this:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20)
GO
UPDATE npidata SET
  npiNew = SUBSTRING(npi, 1, 20)
GO
ALTER TABLE npidata DROP COLUMN npi
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

In this case log file usage can be controlled by UPDATE statement, you may perform this by small chunks like:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20), ready bit
GO
WHILE 1 = 1
BEGIN
  UPDATE TOP (100) npidata SET
    npiNew = SUBSTRING(npi, 1, 20),
    ready=1
  WHERE ready is null

  IF @@ROWCOUNT = 0 BREAK
END
GO
ALTER TABLE npidata DROP COLUMN npi
GO
ALTER TABLE npidata DROP COLUMN ready
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

For better performance, in case when the table is big - you may apply temporary index on column ready

OH, YES!!!

Like @RemusRusanu said - TURN OFF THE AUTOSHRINK

Sql-server – Transaction Log Truncation with Copy Only

I'll be the first to admit the documentation is not very clear on this. They state that you should be offloading your backups to the secondaries, but while most statements are made in a general sense, I think they really mean, specifically, log backups (and copy_only backups, if you have some need for those).

You should occasionally run full backups on the primary IMHO. The copy_only restriction is about full backups, not log backups, and only applies to the secondary AFAIK.

You will not be able to use shrinkfile against your current transaction logs because they are full of activity that has technically not been backed up. Once you take a full (non-copy_only) backup on the primary, then let one log backup run, you should be able to shrink the log file manually. Currently your log backups are working because the database is set to full, but they are continuing to grow because (presumably) you've never run a proper full backup on the primary.

This should be a one-time operation, and you shouldn't shrink them too small; you need to set your primary to be backing up regularly, and you need to accommodate for the largest set of activity that will occur between full or log backups. I won't go on and on about how shrinking files only so they can grow again is a futile exercise and guarantees poor performance, but I could. :-)

Best Answer

Related Solutions

SQL Server – Changing Column Data Type Fills Transaction Log

Sql-server – Transaction Log Truncation with Copy Only

Related Question