2

I have a database with simple recovery mode. For that database I have a job running every morning. This job imports data and executes plenty stored procedures that inserts data, updates records, delete records, makes calculations... The job takes approximately 30 minutes and makes the transaction log be 10GB big. To avoid growth of the log file during the job I set the log size to 11GB. With that amount it is never autogrowing. Nevertheless it is blocking 11GB of disk space.

I just read that putting checkpoints truncates the log. So my idea is to put checkpoints at several location in my stored procedures in order to be able to minimize the size of the log (from 11GB to lot less: 3GB for instance).

Question: is it dangerous to do that take into consideration once again that I will never have to make recoveries?

7
  • 2
    That is pretty common, just be aware that you might have large transactions that require > 3 GB of log, so you still might incur autogrow. It might also be wise to take a full backup before and after this invasive job completes every day. What are you going to do with the other 8 GB of disk space? Personally, I would just leave the log size large, since you're going to have to leave some room there anyway, just in case the log does need to grow beyond 3 GB (unless you're okay with downtime should that happen). – Aaron Bertrand Feb 26 '16 at 21:39
  • Hello Aaron. Knowing that I am in simple recovery and that my log size is 10Gb big does it mean that the biggest transaction of all my transactions requires those 10Gb or is it all my transactions that requires those 10Gb? And if yes are there some tools to identify which query it is? TY – Marc Feb 26 '16 at 21:48
  • are you running out of space, or is the file size just psychologically bothering you? It is bad practice to shrink your log file, because sql server will have to take the time to size it up anyways. – Jeremy Feb 26 '16 at 21:54
  • 1
    Please don't post identical questions to multiple sites in the network. Pick a site, and if you picked wrong, people will help you move it to the right one. – Aaron Bertrand Feb 27 '16 at 1:08
2

For those hitting on that post I actually found a solution to reduce the size of my log by chunking the biggest transactions. That link helped me out :http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

0

In the interest of learning, you can see when the automatic checkpoints are running with an extended events session. You can also see how long they run by tracking both the checkpoint_begin and checkpoint_end events. You shouldn't have to mess around with it too much, though. From the docs:

The default recovery-interval value is 0, which allows the Database Engine to automatically configure the recovery interval. Typically, the default recovery interval results in automatic checkpoints occurring approximately once a minute for active databases and a recovery time of less than one minute. Higher values indicate the approximate maximum recovery time, in minutes. For example, setting the recovery interval to 3 indicates a maximum recovery time of approximately three minutes.

If you're blowing through 10 Gb of log in a minute, you've got other issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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