Transaction log backup file is too big

Question

Transaction log backup file is too big

Viewing 5 posts - 16 through 19 (of 19 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Beatrix Kiddo (2/12/2013)
GilaMonster (2/12/2013)
Beatrix Kiddo (2/12/2013)
I know how to produce a backup file with the date in the name, but where does that leave me when I need it to be overwritten each night?
In a very bad situation if you overwrite the old backup with a backup that proves to be unrestorable...
But surely not if the previous day's backup has been backed up offsite before the file is overwritten each day?

How long does it take to fetch the offsite backup, and how long will you have to restore a DB in the case of a disaster?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

calvo SSChampion Points: 12930 More actions · Answer 2

Beatrix Kiddo (2/12/2013)
I hate this job already :-D.

Congratulations on your new Senior DBA position!

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

Welcome to trial by fire! 😀

For the transaction logs, there's a lot to understand about the different recovery models. This site has a good stairway on the topic at http://www.sqlservercentral.com/stairway/73776/.

For the backups, I would not want to keep a single day's backup for each database and call it good. Look at writing a stored procedure to do your backups for you and schedule it in a database job. That way, you could generate the SQL to do the backup and include the date in the filename. If that seems a bit daunting, (which it should given the other work you have do do at this point) look at Ola Hallengren's maintenance scripts. They are free, very well-respected and maintained. The work's already been done. Anthony.Green included a link to the site it his signature. http://ola.hallengren.com/

My two cents. Welcome to your new position and good luck!

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

calvo SSChampion Points: 12930 More actions · Answer 4

I don't believe anyone has mentioned it but are you using backup compression?

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

calvo (2/12/2013)
Beatrix Kiddo (2/12/2013)
I hate this job already :-D.
Congratulations on your new Senior DBA position!

Made me laugh!

How long does it take to fetch the offsite backup, and how long will you have to restore a DB in the case of a disaster?

It takes about 5 minutes to get the file back, and I would have an hour or two to do a restore in the case of a disaster. This place is fairly slack.

Each day a backup is taken, and then a copy of that is sent to an offsite repository. Daily backups are kept for a month, then weekly ones for 4 weeks, then monthly ones thereafter (if you follow me). So at all times we have the last 30 days' daily backups for each database, as well as weekly ones before that, and monthly ones before that.

I do get your general point though; the current set-up is not ideal. (The backup jobs were already scheduled before I arrived, although there's no documentation to say why they've done what they have- and it probably goes without saying at this point in the thread; the person who did it has left!)

I have loads more reading to do, I think. I'm sure I'll be back as I haven't quite resolved the first issue yet, but in the mean time thanks very much for all the help. I really appreciate it.