1

I inherited lot of old servers and Databases from other department and now I will be the one who needs to take back ups / do Maintenance stuff with it.

I see a lot of databases in simple mode (I checked with the Owners and they say they are fine with loosing one day data in case of disasters)

I see some log files bigger than the databases. I am thinking of Including shrink Log (add a Sql Task ) in my maintenance plan after the back up.

Is it a Good Practice?

Improve this question
5
  • 1
    Just shrink them one time for now and see if they grow again and check how autogrowth is configured in % or MB too. Growth operations are costly but HUGE transactions in a SIMPLE recovery mode DB can cause the LDF file to grow larger than the data files. The root cause of that is probably poorly written SQL queries but if you could find those and fix at that level, then the problem may go away -- batch transactions to be smaller commits. You may just want to shrink each one time and see if they grow that large again too first.
    – IT Thug Ninja
    Oct 7, 2015 at 16:58
  • I shrank the log files manually last couple of days. But every day I receive the alert saying growth occured. I may not be able to update the sql queries they are coming from the application side.
    – user3325655
    Oct 7, 2015 at 17:01
  • Check how autogrowth is setup for each too whether in percentage or MB as if it's some huge number or percentage and need to grow, that would grow it to what you have configured. If the log will grow back to the same size again regardless of what you change or shrink, then you're just temporarily fixing the issue. It may be the application queries are written poorly too and if you have no one to look at that, then you may be stuck with the shrink operation but when it grows again for what it needs, then it'll cost each time so it may not be best practice but you have to do what you have to.
    – IT Thug Ninja
    Oct 7, 2015 at 17:04
  • Auto growth set up as percentage 10%
    – user3325655
    Oct 7, 2015 at 17:12
  • 2
    I would never set auto-growth as 10%. That causes your growth to get bigger and bigger each time. Much better to use some reasonable number like 50mb, 500mb, 1gb, 5gb etc. It really depends on your total log size.
    – Kenneth Fisher
    Oct 7, 2015 at 17:15

1 Answer 1

Reset to default
3

The first thing you have to remember is that all transactions are logged to some degree. There are cases where an action can be minimally logged but there is still some entry in the log.

The recovery modes just change how often the space in the log is freed for re-use. In all cases the log space is freed up once it is no longer needed. In the case of SIMPLE recovery there is no backup required for the space to be freed. However as long as a transaction is active that space won't be freed. In your particular case you probably have some long running transactions (probably batch jobs) that are holding the space open and causing additional data to be added to the end of the log forcing it to grow.

If the logs are taking more space than you are comfortable with your best bet is going to be to examine any batch processes and try to shorten any transactions they are using.

Improve this answer
3
  • I agree 100%, this your best option if you're able to fix at that level--if not though, then you'll need to make a decision how to proceed until you can get a more long-term fix. In a perfect world, you'd always be able to follow the best practices and resolve at levels of the root cause, but sometimes you have to come up with workaround solutions too that aren't best practice to ensure your server stays up and running. Good luck!!
    – IT Thug Ninja
    Oct 7, 2015 at 17:23
  • 1
    Or to be honest, sometimes the log size just is what it is. Unless it's continuing to grow out of hand sometimes the best thing is to just let it be "big".
    – Kenneth Fisher
    Oct 7, 2015 at 17:29
  • Good point.. Indeed!!
    – IT Thug Ninja
    Oct 7, 2015 at 17:30

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.