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 «««1234»»

Transaction Log Backup Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 10:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
MyDoggieJessie (2/14/2013)
midnight251 (2/14/2013)
Great info thanks. What would a sensible size be, 50 gig? I did notice a 'Shrink File' in the Maintenance Plan, is this what I use for the one time shrink? What happens if I try to shrink the file past what the size actually is? Sorry for all the questions, but, we're almost home.....
A good rule to follow for sizing your transaction log file would be to find out what your largest clustered index size is for that particular instance, and size your file to that size (preferably a little more). That way when your indexing strategy kicks off, you hopefully won't have to grow your log file while your larger indexes are being rebuilt.

That's kind of OK if you're in SIMPLE recovery mode. When you're in FULL recovery mode chances are good that more than one index will be rebuilt in between your log backup schedule.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420202
Posted Thursday, February 14, 2013 10:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 3,893, Visits: 7,138
Of course, I agree. Just saying as a general rule...more of a when in doubt - For Full Recovery DB's I run log backups every 15 minutes so generally don't have much of an issue with this. For the sake of the OP, if there's a table that's 80GB in size, there wouldn't be much point in making the log file 50GB.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1420207
Posted Thursday, February 14, 2013 10:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
MyDoggieJessie (2/14/2013)
Of course, I agree. Just saying as a general rule...more of a when in doubt - For Full Recovery DB's I run log backups every 15 minutes so generally don't have much of an issue with this. For the sake of the OP, if there's a table that's 80GB in size, there wouldn't be much point in making the log file 50GB.

Agreed for the lower boundary. The upper boundary is the tricky one.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420211
Posted Friday, February 15, 2013 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
This is great info folks. I want to thank you again for all the help. Log files are getting backed up nicely every hour on the hour, full DB backup getting done twice in 24 hour period (sleeping better at night). I'll probably get at the re-size of the log file next week (we have a long weekend here in the Great White North). Just curouis as to how long you guys keep your log backup files around for? The first log file that got generated in my case is just over 38 gig.

Thanks again
Post #1420748
Posted Friday, February 15, 2013 2:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
Nice work. For keeping log backups around...it depends on how long you need to retain point-in-time recovery. I usually keep mine around for ~10 days, or whatever my log shipping retention period is for that server. After that I would need to go back to the operating system tape backups that scoop complete drive images nightly.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420755
Posted Thursday, February 21, 2013 8:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
opc.three, you got me thinking about point-in-time recovery. If DB fails, would you not just restore the lastest full backup, do a tail end backup of the log file, and then start to restore the log files up to the point of failure, then restore the tail end backup? I guess I'm thinking, why would you want to go back further past the last full backup, (unless the full backup failed)?
Post #1422634
Posted Thursday, February 21, 2013 8:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
Your approach would be correct if you were in a disaster recovery scenario and wanted to recover as much data as possible.

As for why you would want previous log backups, say someone noticed data missing from an important table and it was traced back to an errant delete carried out by a person or an application and you needed to recover that data just before the delete occurred, and that delete occurred 4 days ago...


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422646
Posted Thursday, February 21, 2013 1:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Interesting. I didn't know you could take it down that far and only restore certain data to certain tables. We're running MS NAV 2009 ERP on SQL, and if something gets deleted , it's deleted from this table and that table, etc. And with MS not releasing the schema, it's almost impossible to know all the tables it hits. You do have a valid point.
Post #1422766
Posted Thursday, February 21, 2013 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
midnight251 (2/21/2013)
Interesting. I didn't know you could take it down that far and only restore certain data to certain tables.

You can't restore data for specific tables without the help of some third-party tools. What you would have to do is restore the relevant FULL backup taken prior to the DELETE and then restore each log backup from that point forward one at a time WITH STANDBY, checking for the missing data, and then when it first disappeared from the database you'll have identified which log file logged the DELETE operation. You would then need to repeat the process starting with the FULL backup and all the logs minus the last one you restored...and then you could restore that last one with the STOPAT option to get as close to that point-in-time before the delete occurred. Of course I am describing a nightmare scenario for a DBA, one I've had to do but where I pretty much knew within a few minutes when the data was deleted so it wasn't a huge deal, but some folks are not so lucky.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422775
Posted Thursday, February 21, 2013 1:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 2,827, Visits: 8,470
opc.three (2/21/2013)
midnight251 (2/21/2013)
Interesting. I didn't know you could take it down that far and only restore certain data to certain tables.

You can't restore data for specific tables without the help of some third-party tools. What you would have to do is restore the relevant FULL backup taken prior to the DELETE and then restore each log backup from that point forward one at a time WITH STANDBY, checking for the missing data, and then when it first disappeared from the database you'll have identified which log file logged the DELETE operation. You would then need to repeat the process starting with the FULL backup and all the logs minus the last one you restored...and then you could restore that last one with the STOPAT option to get as close to that point-in-time before the delete occurred. Of course I am describing a nightmare scenario for a DBA, one I've had to do but where I pretty much knew within a few minutes when the data was deleted so it wasn't a huge deal, but some folks are not so lucky.


In a situation like this, you restore your database to a temprary database so as not to affect your current live database. So you get your full backup and restore it as "MyProductionDatabaseName_TempRestore" or some such. When you have it restored from the correct time, copy the missing table data from "MyProductionDatabaseName_TempRestore" to "MyProductionDatabaseName".

As for all your transaction log backups, there's a setting in the Maintenance Plan for how long you want to retain the log backup files. 10 -14 days is fairly common.



Post #1422779
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse