SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction log backup file is too big


Transaction log backup file is too big

Author
Message
OTF
OTF
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 4128
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.
Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2690 Visits: 4391
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.)
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10056 Visits: 6324
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
When a question, really isn't a question - Jeff Smith
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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, 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


Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2690 Visits: 4391
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 :-D.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1808 Visits: 4008
Beatrix Kiddo (2/12/2013)

I hate this job already :-D.


Congratulations on your new Senior DBA position!

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16506 Visits: 10059
Welcome to trial by fire! :-D

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
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1808 Visits: 4008
I don't believe anyone has mentioned it but are you using backup compression?

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2690 Visits: 4391
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search