When is the log truncated?

Question

When is the log truncated?

RSingh-135921

Default port

Points: 1438
More actions
May 21, 2007 at 1:59 pm

#61304

I have a database with following properties:
DataFile Size: 493 MB
Transcation Log Size: 15.6 GB
Recovery Model: FULL
When I took a full backup of the database the backup size is 247 MB. Immediately after that I took a transaction log backup and its size is 14 GB.
Why this huge difference?
AFAIK when a full backup is taken it also copies the transaction log, then why is the full backup size so small and transaction log backup size so large?
Also when a full backup is taken the log is truncated then why is the log backup size in gigs although it has been truncated?
Pls advise.
Regards,
RSINGH

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 1

I believe you are a bit confused.

Backing up the database does nothing to the transaction log I believe. You have to back up the two separately. Also, the two could have different sizes.

The transaction log is truncated when you back up the TRANSACTION LOG FILE (at least this is one scenario of when it is truncated). You can shrink the transaction log if you like (i.e. you believe it is too large). Just keep in mind that it will grow again, if it needs to, and it would be costly when it is doing so.

Hope that helps - if not - state more of your confusions and I'll try to clarify ...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Just to confirm, a full backup only backs up enough of the transaction log to ensure a consistant database when restored. The transaction log backup will truncate all inactive transactions after the transaction log is backed up.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Truncating the tlog depends on the Recovery Mode being used.

Full Recovery Mode: A full backup will not cause the tlog to truncate. The tlog will only truncate when a log backup is done.

Simple Recovery Mode: A full backup WILL cause the tlog to checkpoint and subsequently be truncated. The tlog will also checkpoint upon commited transactions, but will only overwrite itself and not truncate until a Full backup is done.

-SQLBill

RSingh-135921 Default port Points: 1438 More actions · Answer 4

Thanks to everyone who replied.

Actually I was mislead by the following statement:

"Full Backup

The full backup does just that – it backs up all the data in the database. Every table, stored procedure and all other objects in the database are placed into a single backup file on a hard drive or tape. This operation also truncates the transaction log, and a separate log backup isn't necessary. "

in this article:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=40&rl=1

Thanks for the clarification.

Regards,

RSINGH

mngong SSCertifiable Points: 6167 More actions · Answer 5

In some cases you may have to truncate your transaction log to free up space.

At least in my case when you backup a 456M database with a 15G transaction log a restore of the database will recreate a T log the same size.In the cases where the transaction log is very big it is backed up and shrinked right before a full backup

since space has been one of my issues with the restores of the larger files

Mike

Antares686 SSC Guru Points: 125444 More actions · Answer 6

I suggest you look at "Transaction Log Backups" in SQL BOL.

As for truncating, I think it is a bit of a misnomer. With the TL is truncated it does not mean it is also shrunk, so a 15GB TL can be truncated thru the backup process and still be 15GB. It just makes the space availabel for new transactions without growing the file until full again.

Jim Howell Ten Centuries Points: 1010 More actions · Answer 7

We use this script to shrink the log files--

replace the database name and use the logical log file name and you are in buisiness.

USE accutrack

GO

CHECKPOINT

GO

BACKUP LOG accutrack WITH TRUNCATE_ONLY

GO

DBCC SHRINKFILE (accutrack_log, 10)

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 8

I don't think it is a good idea to shrink the TRANSACTION LOG file. If you do do it, you have to have a reason, such as:

"It grew due to something which you would not expect to happen again or frequently"

Otherwise, leave it alone (or add hard disk space, if this is the problem).

cgarnold SSC Rookie Points: 49 More actions · Answer 9

The only reason I can see for the transaction log file being that much larger than the data file is that the database has been running for a while without regular log backups being done. Now that you've backed up the log and therefore truncated it, you should be able to see that the space used is a small fraction of the total file size. Now you might want to shrink the log file down to a more reasonable size, depending on your particular database and its usage. Then set up regular transaction log backups so that it gets truncated more often and won't need to keep growing. Again, it depends on your usage as to how often you should run the backups. I have some DBs that I only run log backups on once a week, and then others that are done every hour. Additionally, having current log backups in addition to your full backups makes it possible to recover closer to the point of failure in case of emergency.

I hope this helps.

Cary

RSingh-135921 Default port Points: 1438 More actions · Answer 10

Thanks everyone.

Cary - You are also absolutely right. The log backups of the database had not been taken since long. A subsequent log backup after the first one was quite small in size.

I have now scheduled regular tlog backups along with full ones.

Thanks once again !!!

RSINGH

Norberto Gamboa da Silva Grasshopper Points: 12 More actions · Answer 11

I have a script for you.

DECLARE @LogicalFileName sysname,

@MaxTime tinyint,

@MaxMinutes tinyint,

@NewSize int,

-- Setup / initialize

@OriginalSize int,

-- Wrap log and truncate it.

@Counter int,

@StartTime DateTime,

@StartMin DateTime,

@TruncLog nvarchar(255),

@DB sysname,

@StmtSQL nvarchar(300)

SET NOCOUNT ON

-- Limit on time allowed to wrap log. in MB

SET @MaxTime = 10

SET @MaxMinutes = 1

SET @NewSize = 2

SET @StartTime = GETDATE()

SET @DB = db_name()

SET @TruncLog = N'BACKUP LOG ' + @DB + ' WITH TRUNCATE_ONLY'

-- Use sp_helpfile to identify the logical file name that you want to shrink.

SELECT @LogicalFileName = RTrim(name),

@OriginalSize = size

FROM sysfiles

WHERE GROUPID = 0

SET @StmtSQL = N'SELECT ''Original Size of LOG is ' +

CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'' AS ' + @DB

Exec sp_ExecuteSQL @StmtSQL

WHILE (@OriginalSize*8/1024) > @NewSize

AND @MaxTime > DATEDIFF(mi, @StartTime, GETDATE())

BEGIN

CREATE TABLE DummyTrans( DummyColumn char (8000) not null )

SET @StartMin = GETDATE()

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

Exec sp_ExecuteSQL @TruncLog

-- Wrap the log if necessary.

WHILE @MaxMinutes > DATEDIFF(mi, @StartMin, GETDATE()) -- time has not expired

AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk

AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.

BEGIN -- Outer loop.

SET @Counter = 0

WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN -- update

INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.

DELETE DummyTrans

SET @Counter = @Counter + 1

END -- update

PRINT 'FIM 3º LOOP'

Exec sp_ExecuteSQL @TruncLog -- See if a trunc of the log shrinks it.

END -- outer loop

PRINT 'FIM 2º LOOP'

DROP TABLE DummyTrans

SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName

END

PRINT 'FIM 1º LOOP'

SET @StmtSQL = N'SELECT ''Final Size of LOG is ' +

CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'' AS ' + @DB

Exec sp_ExecuteSQL @StmtSQL

PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 12

Hi,

the main reason to have a database set to "full recovery" is to be able to do a point-in-time recovery.

Imagine the following scenario:

You do a full backup,

you do log backups,

you do a backup with "truncate_only",

some users are writing to the db,

you start a full backup.

During the backup the server fails.

The result is that you are not able to restore the users data because the LSN-chain is broken!

You should only use "truncate_only" in one case:

the log file is full and you can't grow it and you have no way to do a log backup to disk or tape!

</rant on>

As long as you have access to disk space plan to do a normal log backup in any case! You can allways throw away a backup file, but you can't create one if it's missing...

regards

karl

Best regards
karl

SunnyFLADBA SSC Enthusiast Points: 113 More actions · Answer 13

There are two factors to consider with tranactions logs 1) Truncating 2) Shrinking

If your Transaction Log is 15GB it will stay 15GB until you shrink it. If you do a full backup of the database (full recovery mode) there is no need to immediatly backup the transaction log because it is empty. Note: it still has 15gb of reserved space to utilize. So a full backup does truncate the transaction log, It does not however shrink or free up the reserved space i.e. 15GB. If you wanted to reduce the size of the transaction log you can Issue a dbcc shrinkfile command.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Joseph, A full backup does not trunctate the transaction log. A full backup only backs up enough of the transaction log to provide a consistent database during a restore. In full recovery mode, a transaction log backup needs to be done to truncate the log file.