| Author |
Topic |
|
thierryVD
Starting Member
11 Posts |
 Posted - 2007-07-23 : 07:44:56
|
| Does the following make sense if one needs to shrink a log file: - put db in Simple recovery mode - shrink log file - revert db recovery model back to Full?I noticed this at one of our customers. In my opinion there is a chance to loose transactions if something goes wrong. Especially when there would be a need to restore to a point in time. Doesn't this break the transaction log chain? Apart from the fact that I don't feel comfortable with this strategy and it seems odd, I also suppose a full db backup would be in place... after putting the db in simple recovry model, right? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-23 : 10:28:56
|
| this is a bad solution, unless this database doesn't really need to be in full logging mode. the tlogs before the change to simple mode are now gone (unless database was placed in single-user and tlogs backed up first). Even if you saved these logs, they now have no connection to the logs created when you change back to full mode. this means that you can no longer perform a recovery that crosses this moment in time (change from full/simple/full) using the tlogs you have backed up.if you do do something like this it is imperitive that you immediately back up your database once you go back to full mode. A full recovery of data will not be possible unless you do this.-ec |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-23 : 11:02:06
|
| If they want to shrink the log file, don't bother putting it in simple recovery mode, just execute DBCC SHRINKFILE.More than likely, they shouldn't shrink it at all. Just let it stay at the size it grows to.CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-23 : 13:56:02
|
| Do log backup first. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-23 : 15:05:47
|
quote:
Originally posted by rmiao
Do log backup first.
see my post. that does you no good since you can't cross the full/simple change boundary for recovery.-ec |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-23 : 16:41:15
|
| You should backup log before shrink it no matter which recovery mode the db is. |
|
|
|
thierryVD
Starting Member
11 Posts |
Posted - 2007-07-24 : 05:51:03
|
| Hi guys, thanks for your replies. It confirms my feelingBTW, rmiao, log cannot be backed up while in simple recovery |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-24 : 08:40:07
|
| "log cannot be backed up while in simple recovery"Yeah, but they should be backed up before switching to simple recovery.Either way, doing a Shrink in anything but an exceptional circumstance (e.g. after a one-time massive delete, or some accidental log growth) is a very bad idea.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-24 : 14:49:59
|
| You can do following when the db is in simple recovery mode:backup log db_name with truncate_only |
|
|
|
|
Shrink log file strategy