SQL Backup Job

Question

SQL Backup Job

shri_sastry

SSC-Addicted

Points: 472
More actions
March 9, 2009 at 8:04 am

#128256

Hello all,
When a SQL Full backup is taking place, is it OK to run a trnasalcation log backup job on the same database or should I disable the transaction log backup job when the full backup job is running also the log file is not getting truncated/shrunk at the end of a transacation log backup, do I have to specify the truncate/shrink in the transaction log backup job???
Thanks For all your help
Shri

Viewing 8 posts - 1 through 7 (of 7 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can't run a tran log backup at the same time as a full backup. Won't let you.

When you say the tran log isn't being truncated/shrunk, I'm assuming you mean it isn't shrinking. Truncation is a different thing. Best bet, in almost all cases, is don't shrink the log after backup. It'll just have to grow again, and that'll slow down your database. Give it enough room, back it up frequently enough, and you'll be fine.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

shri_sastry SSC-Addicted Points: 472 More actions · Answer 2

Thanks for the response, I also thought that the transaction log backup won't start until the full backup is done, but it did on my server. The full backup started at 10.00 PM friday night and returned a failed messge at 12:15 AM, but a transaction log backup started at 12:00 midnight and completed in 4 minutes. That was what surprised me. I am backing up on to a network share and this backup ran fine a few times but all o a sudden this weekend I got this error. Do you think I should just disable the transaction log job and renable it after the full backup is done

Thanks for any suggestions

Shri

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

If your using SQL 2005 you should build these actions into a maintenance plan (SSIS based). This way you can control the flow. I.e the t-log backup wont trigger until the db backup has completed.

Though to be honest theres not to much point in backing up t-log straight after the main database (other then shrink). Are you backing up t-log throughout the day? Is this a requirement for your business? (if you lost the database at midday, is it acceptable to go back to the previous night backup)

Adam Zacks-------------------------------------------Be Nice, Or Leave

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

shri_sastry (3/9/2009)
Do you think I should just disable the transaction log job and renable it after the full backup is done

I wouldn't be enabling/disabling backup jobs, as a DBA you have bigger fish to fry.

Just plan, schedule and test a backup strategy that fits your business recovery needs... let it run alone and have a junior guy checking logs in a daily basis.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 5

Its Ok to run a tran log backup job at the same time as FULL backup. The tran log backup job will wait for the FULL to finish.

thanks

SQL_EXPAT

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

Transaction log backups does not disrupt Full backup process. You can definitely issue tlog backups.

But the question is, Why do you have to take Tlog backups immediately after a Full backup?

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 7

I can't talk for others but many times you have transaction log backups configured to run quite regularly like every hour - 24 hours/day. Log shipping is an example. Often a log backup schedule will kick in when a FULL is running. This is no problem. There are more important things for a DBA to do then figuring out exact windows for log backup schedules.

thanks

SQL_EXPAT