| Author |
Topic |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2006-03-20 : 10:07:52
|
| I need some clarification on log backups and shrinks.I have a 40 gig db, its pretty active so the Log is backed up every 2 hours. Does the log get cleared out after a backup so that space is reused for transactions? As such, I need to keep the log under 10gig. So can I have a shrink every 2 hours following the backup to say 5 gig? What is a good schedule for shrinking?________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-03-20 : 10:20:47
|
quote:
Originally posted by nr
seehttp://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Very good, thanks. need to read into recovery modes.________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-20 : 11:55:18
|
| If your transaction log is that big, you should be running transaction log backups much more often. Running them every 5 to 15 minutes will keep the transaction log size smaller, and you will be able to recover closer to a failure point.CODO ERGO SUM |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-03-20 : 12:01:48
|
quote:
Originally posted by Michael Valentine Jones
If your transaction log is that big, you should be running transaction log backups much more often. Running them every 5 to 15 minutes will keep the transaction log size smaller, and you will be able to recover closer to a failure point.CODO ERGO SUM
Yeah, I just inherited this mess so I am going to certainly review whats going on and work on it. ... In Full recovery mode, just backing up the log isn't going to keep the size down. Perhaps a shrink twice a day would help.________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-20 : 12:21:27
|
quote:
Originally posted by Vivaldi... In Full recovery mode, just backing up the log isn't going to keep the size down...
I am not sure what led you to this conclusion. If you backup the log at short intervals, it should not grow as much bacause it will need to hold fewer transactions.In any case, you really should not be shrinking it, because it will just grow again. Let it take the space it needs.CODO ERGO SUM |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-03-20 : 12:24:09
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by Vivaldi... In Full recovery mode, just backing up the log isn't going to keep the size down...
I am not sure what led you to this conclusion. If you backup the log at short intervals, it should not grow as much bacause it will need to hold fewer transactions.In any case, you really should not be shrinking it, because it will just grow again. Let it take the space it needs.CODO ERGO SUM
Hmm. Well, the SAN Admin kind of holds the keys to the drive size castle.... I am only shrinking the thing down to 5 gig. (which hopefully it won't reach anymore...)________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
Kristen
Test
22859 Posts |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
|
|
|
Log Backups and Shrinks
Kristen