1

I have a situation that I need some advice to overcome.

I have a SQL Server 2012 database that has been set to Simple recovery model. At times, my SQL Server transaction log file grows out of control and tries to consume all available disk space. It shows as empty in SQL Server, but continues to consume disk space (I understand why). I would like to know if there is a way to prevent SQL Server from maintaining the log file at all.

The application that lives in front of this database doesn't play well with the idea of recovering using a trans log backup. Our backup strategy is one of Full and Diff backups executed on a regular enough schedule that we are adequate covered. We don't need or want point in time recovery (hence simple recovery).

To test, I had the log file capped at 10mb, and 10% auto-growth turned on. My SQL Server load was working away at about 3.8mb trans file, when I initiated a full DB re-index (to try to approximate the load that our customers may put on the database). The log file went straight to 10mb+, and all transactions halted with a '9002' error.

I believe I understand the goal of the trans log in normal situations, but in my case, it is just taking up space and getting in the way. Is there a way to prevent SQL Server from using it?

Thank you in advance for your insight!

Catt11.

migrated from stackoverflow.com Sep 19 '14 at 6:22

This question came from our site for professional and enthusiast programmers.

  • 4
    " Is there a way to prevent SQL from using it?" - No. – Mitch Wheat Sep 19 '14 at 5:56
  • 1
    " The log file went straight to 10mb+, " - Huh? That's tiny. I have log files as big as 100GB (presized of course) – Mitch Wheat Sep 19 '14 at 5:56
  • SQL Server doesn't let it. But you can shrink log files if you'd like to save disk space. Check this link for more information. – oardic Sep 19 '14 at 5:58
3

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

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.

This means that a large transaction can still expand the log, if the current size of the log is too small.

You need to 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. Do not regularly shrink your transaction log.

The best way to get the correct size for your transaction log is to simulate production traffic on a development server. Run queries (that would be executed against your production database) against your development database for a set period of time, and monitor the log.

0

You can check the %age of log space used first by running command

DBCC SQLPERF(LOGSPACE).

The check space used column which seems high percentage, in this case it will be your database

Shrink the log file by DBCC sHRINKFILE([dblogfilename],1)

and run above command, to check how much it reduced.

0

I did not see complete answer to your question so I would add an answer.

I would like to know if there is a way to prevent SQL Server from maintaining the log file at all.

No not possible every transaction in SQL server is logged in some way. Logging depends on recovery model you use. In simple recovery model almost enough information would be logged just the transaction log backup semantics are different.

To test, I had the log file capped at 10mb, and 10% auto-growth turned on

I think this is the culprit here although a long running transaction in simple recovery can still hold log hostage and would not allow it to truncate.There is a nice discussion about Why does Transaction Log file grow and runs out of space You must have a look at this. Please change auto growth setting to MB. 10 % growth would allow SQL server to capture more space when growing even if less space is required because of the value you have set.Take some time and read This article to understand autogrowth and calculate appropriate value of autogrowth

Your Answer

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