Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
monty
Posting Yak Master
130 Posts |
 Posted - 2006-10-05 : 01:20:31
|
we have a database of size 15GB and the tlog is growing at a steady level at present it is about 6.8GB, now my question is we have a full backup sheduled daily at 12:30 AM and tlog backups from 3AM-11:59PM at a frequency of 2hrs And the total disk space is 42GB and free space on it is 13GB we retain only previous days backup.If we decrease the frequency of tlog backup to 1hr with the same shedule do you think it wud be a better practice, here im refferring to a gud practice were in i wont run out of space. with luvmontyits me monty |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-05 : 02:25:21
|
Hi monty, welcome to SQL Team!Are you happy to lose 2 hours work? 1 hours work?  I'd set it to 10 or 15 minutesIf you make 6GB of Log Data in 2 hours it doesn't make a lot of [disk space] difference whether you back it up as one 6GB chunk or 8 smaller chunks totalling 6GB. It is more effort to restore of course because there are more files to script/mouse-click - but hopefully you don't have to recover very often!As your tlog size is growing I expect that there is some activity that needs that space. It might be the maintenance utilities rebuilding indexes etc, and increasing the frequency of TLog backups may start introducing backups in the middle of those routines and reduce the need for a large portion of your TLog file.Note that a bigger-than-required TLog file is no problem - SQL only uses what it needs of course - and extending it is an "expensive" task, so you don't want to shrink it except in exceptional circumstances. However, a RESTORE to a new database will need to pre-initialise the whole 6.8GB, even if there is no current need for it, so that might represent a reason to investigate more closely - as a restore to new database is likely to be needed in response to a disaster, and would be time-critical!Having said all that I reckon that a LOG file of up-to 120% of the DATA file is "OK", so you are well inside that metric.Kristen |
 |
|
|
monty
Posting Yak Master
130 Posts |
Posted - 2006-10-06 : 01:38:14
|
| Hi Kristen,thanks 4 the detailed xplanation, im really happy 4 the fisrt time 4 such a useful datailed note... ur thoughts on recovery are really appriciable.its me monty |
|
|
|
|
|
|
|
|
Backup's