4

I am wondering: We have a Database on our MS SQL Server 2012. The recovery mode is "simple".

The DB itself is about 2.7GB big. I have an MAX Size for Trn-Log configured (3GB).

Now i made a Maintenance Plan with "reorganize Index Task" --> "Update Statistics".

The strange thing ist that when i run the Maintenance Plan the Log of the DB in "simple" Mode grows and grows since the 3GB are filed and i get an error...

Why the Trn-Log is used even in simple mode? And why the 3 GB seems to be not enough?

1 Answer 1

7

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log.

There is no way to prevent SQL Server from writing to the transaction log.

This means that a large transaction can still expand the log, if the current size of the log is too small. The tasks in your maintenance plans are performing operations in the database which are being written to the log, which is causing the log to expand, hit the 3GB limit you have set and that generates the error.

Remove the 3GB limit on the size of the log and make sure that the drive your log is on has enough adequate free space for the log to expand, or (and this is what I recommend) expand your log file manually to a size that will avoid auto-growth.

Finally, even though your database is small, I would not recommend using maintenance plans. The tasks in them are not "smart" and will perform operations even if they are not needed.

For example, the REBUILD INDEX task will perform a index rebuild even if it is not required (the index's fragmentation is low). This will cause a lot of information to be written to the database's log file.

Have a look at other maintenance solutions, I use Ola Hallengren's scripts.
https://ola.hallengren.com/

0

Your Answer

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

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