Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Transaction log backup file is too big Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 4:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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
Posted Tuesday, February 12, 2013 4:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 510, Visits: 1,895
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
Posted Tuesday, February 12, 2013 5:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
Posted Tuesday, February 12, 2013 6:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Tuesday, February 12, 2013 7:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 510, Visits: 1,895
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 .
Post #1418980
Posted Tuesday, February 12, 2013 7:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Tuesday, February 12, 2013 8:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 6:20 AM
Points: 1,269, Visits: 3,635
Beatrix Kiddo (2/12/2013)

I hate this job already .


Congratulations on your new Senior DBA position!


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1418987
Posted Tuesday, February 12, 2013 8:31 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 4,618, Visits: 4,076
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
Post #1419004
Posted Tuesday, February 12, 2013 8:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 6:20 AM
Points: 1,269, Visits: 3,635
I don't believe anyone has mentioned it but are you using backup compression?

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1419013
Posted Tuesday, February 12, 2013 8:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 510, Visits: 1,895
calvo (2/12/2013)
Beatrix Kiddo (2/12/2013)

I hate this job already .


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 »

Add to briefcase ««12

Permissions Expand / Collapse