I have a database running under the simple recovery model. The database contains three narrow tables. For two of the tables, data is populated by insert statements. The third table is initially populated by an insert statement, and then the record is completed by an update statement. On average, the database sees 4000 inserts per minute and 2000 updates per minute. It's active, but not exceedingly so.

The database has 10GB of space allocated to the transaction log. According to the Disk Usage report / DBCC SQLPERF(LOGSPACE), the log space usage is steadily increasing. Yesterday, usage was at 33% and growing. Overnight, it has increased to 48% and continues to rise. DBCC OPENTRAN returns no open transactions on the database. Nothing stands out to me when I run sp_whoisactive.

Another database on the same server, with similar activity, a slightly larger transaction log size, and simple recovery model, is cycling between 0% and 10% usage, which is the behavior that I am used to seeing for the first database. What other steps can I take to determine the cause of this behavior and ensure that the transaction log will not fill up entirely?

*Given that I cannot justify the growth in log usage, please assume that "allocating more space to the transaction log" is not a viable solution.

  • 2
    What is the value of log_reuse_wait_desc for that database in sys.databases? – Scott Hodgin Feb 13 '18 at 15:32
  • @ScottHodgin It comes back as NOTHING. Also, just like that, the usage dropped down to 0 again. Can I take that NOTHING to mean that there is nothing holding up the transaction log, and rather that SQL Server is just waiting until when it thinks is best to verify the contents of the log and reclaim that space? – mathewb Feb 13 '18 at 15:44
  • Check the answer associated with Why is my transaction file growing when db is in simple mode and there are no waiting transactions to see if you have the same situation. – Scott Hodgin Feb 13 '18 at 15:49
  • @ScottHodgin Not the same. It displays (100 - space used)% free, which is what I'd expect. The file itself isn't growing/hasn't grown, because I pre-sized it accordingly. I'm trying to make sure that the entire 10GB doesn't get used up for some reason that I haven't accounted for. – mathewb Feb 13 '18 at 17:33
  • 6,000 transactions a minute is not the stock exchange but that's still a lot of activity that doesn't leave a whole lot of time for entire portions of the transaction log to become inactive. – Aaron Bertrand Feb 13 '18 at 20:42

Your Answer

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

Browse other questions tagged or ask your own question.