1

I have a database for staging purpose before move into another main database. It has 5-6 millions records on average. The transaction log keep growing after few days to 30-40 gig. The transaction record not important because I can retrieve from my source again if needed. There are some configuration details needed only.

What's the best way to manage transaction log ? What database recovery mode should I set - Simple, Full, Bulk Logged ? By default, I selected Full. Should I just schedule a job to purge the transaction log on a daily basis ?

Any recommendation ? Thanks.

Improve this question
1

2 Answers 2

Reset to default
4

The transaction record not important because I can retrieve from my source again if needed. . . . What database recovery mode should I set

SIMPLE. Then shrink the log one time.

Improve this answer
1
  • @user3015739 The key phrase here is "one time". Shrinking your log file shouldn't be something you're doing routinely, mostly because it's a heavy operation and if the log is going to re-grow anyway due to your normal database usage then shrinking was a wasted operation, especially because the growth operation is a heavy operation as well, so they're both events that are performance impacting on your database. It's ok to shrink in special one-off cases like this when switching recovery models as a start with a clean slate though.
    – J.D.
    Commented Feb 18, 2021 at 12:51
1

How much data can you afford to lose in case of a damaged database (i.e., you have to do restore). I.e., what is your RPO? That decides the frequency of your backups.

If only full backup (and possibly also differential) is enough, then set to simple.

If you need more frequent backups, then set to full and do regular log backups.

After above has been settled, shrink the log file to a suitable size.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.