Transaction log backup file is too big

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Transaction log backup file is too big

20 posts, Page 2 of 2

««1 2

Transaction log backup file is too big

Rate Topic

Display Mode

Topic Options

Author

Message

OTF

Posted Tuesday, February 12, 2013 4:28 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:56 AM
Points: 254, Visits: 3,711

I would also add that if you have just taken over your environment then you will probably need to be as interested in your ability to restore your current backups as your backups/scheduling.
Its a good idea to get yourself a test environment and attempt to restore what you have to ensure you can.
Its an ongoing best practice, I would think anyway.

Post #1418866

Beatrix Kiddo

Posted Tuesday, February 12, 2013 4:49 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 19, Visits: 62

anthony.green (2/12/2013)

But it still sounds like your backing up to 1 big file and not individual files.

The database or the transaction log, is this? The space issues we have mean that we need to overwrite the backup every night, so it was initially set up (not by me) like this;

BACKUP DATABASE RLT TO DISK='E:\RLA_BACKUPS\RLTBACKUP.BAK'
WITH INIT, FORMAT, NAME = 'Full Database Backup', STATS = 10

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?

(And don't worry, I've moved over to the dev environment for now.)

Post #1418877

anthony.green

Posted Tuesday, February 12, 2013 5:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

You will need to build in a clear down routine to remove files older than so many days.

Again Ola's scripts include this by passing in a value to the CleanupTime variable.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1418900

GilaMonster

Posted Tuesday, February 12, 2013 6:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 37,648, Visits: 29,899

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...

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1418935

Beatrix Kiddo

Posted Tuesday, February 12, 2013 7:54 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 19, Visits: 62

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?

I hate this job already BigGrin

Post #1418980

GilaMonster

Posted Tuesday, February 12, 2013 7:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 37,648, Visits: 29,899

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 2008, MVP
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

Post #1418982

calvo

Posted Tuesday, February 12, 2013 8:02 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:31 AM
Points: 1,157, Visits: 3,077

Beatrix Kiddo (2/12/2013)

I hate this job already BigGrin

Congratulations on your new Senior DBA position!

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.

Post #1418987

Ed Wagner

Posted Tuesday, February 12, 2013 8:31 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,856, Visits: 528

Welcome to trial by fire! BigGrin

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!

Post #1419004

calvo

Posted Tuesday, February 12, 2013 8:45 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:31 AM
Points: 1,157, Visits: 3,077

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

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.

Post #1419013

Beatrix Kiddo

Posted Tuesday, February 12, 2013 8:47 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 19, Visits: 62

calvo (2/12/2013)

Beatrix Kiddo (2/12/2013)

I hate this job already BigGrin

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.

Post #1419017

« Prev Topic | Next Topic »

20 posts, Page 2 of 2

««1 2

Permissions