Transaction Log Backup

Question

Transaction Log Backup

sstecher

Ten Centuries

Points: 1057
More actions
November 25, 2003 at 7:36 am

#107415

Recently I changed two of my databases from SIMPLE Recovery Mode to FULL Recovery Mode. My question is.
Will running transaction log backups every two hours WITH INIT, NOSKIP keep the transaction from growing too large?
Reason I ask is we had a database that was set to FULL Recovery Mode and no transaction log backups. After seeing several problems I noticed that the transaction log grew to 63Gb.
Or does anyone have any better ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

quote:
Will running transaction log backups every two hours WITH INIT, NOSKIP keep the transaction from growing too large?

Yes, probably. Cannot say for sure without knowing what is "too large" and what are the most changes you may make to the database within a two hour period or one transaction.

If you're using WITH INIT, then you also need to use a different name for each log backup, or they will be worthless after the first one following a database backup is overwritten.

--Jonathan

jefjonesdba Valued Member Points: 54 More actions · Answer 2

What will keep your transaction log small is running Backup Log every 2 hours. The WITH INIT, NOSKIP has no impact on the transaction log size. WITH INIT will delete the previous log backup which is not what you want to do for recovery.

Jeff

kelly.settle Newbie Points: 3 More actions · Answer 3

After every full backup I automatically run these 2 commands to reclaim the space the TLog has allocated:

backup log DBName with truncate_only

DBCC SHRINKDATABASE (N'DBNAME', 0,TRUNCATEONLY)

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

quote:
After every full backup I automatically run these 2 commands to reclaim the space the TLog has allocated:
backup log DBName with truncate_only
DBCC SHRINKDATABASE (N'DBNAME', 0,TRUNCATEONLY

Have you ever tried a test restore?

If you truncate the log without backing it up (as with your statement), you have broken the chain of tran logs and any subsequent tran log backup is worthless. I suggest instead backing up the tran log before your full backup. That will truncate the log and make one fewer log file to apply when recovering from the backup.

If you let your database and log files autogrow only to constantly reshrink them, you're harming performance in two ways:

This virtually guarantees that the files will become fragmented on disk, causing the drive heads to have to stop reading or writing and reposition often.
Your files will be forced to autogrow often, which uses CPU cycles (and stalls disk I/O).

--Jonathan

Edited by - Jonathan on 11/26/2003 08:02:31 AM

--Jonathan

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 5

I backup my transaction logs at 10 am, 2 pm, 6 pm, and 10 pm, as well as just before the full backup. I use the following code to ensure that the first trans log (10 am) made since the first backup wipes the previous translogs (FYI though, all the trans logs have already been backed to tape by this point):



IF DATEPART(hh, Getdate())=10 BEGIN



BACKUP LOG [dbCCC] TO [CCC_Log]

WITH INIT, NAME=N'dbCCC backup L'



END

ELSE BEGIN





BACKUP LOG [dbCCC] TO [CCC_Log]

WITH NAME=N'dbCCC backup L'

This works very well. I test it monthly, and just a couple of weeks ago had my first real restore. Restored the full backup and the two trans log backups with no problem.