1

I am in the position of "acting" DBA since our previous DBA left. I haven't had any formal training or the like in SQL Server. I've searched on TempDB autogrowth terms and can find a lot of information about how to turn it on, how to shrink dbs etc, but not specifically about the issue I have, if it's normal, or if there's a bunk setting somewhere. I'm hoping some of you Stack Overflow Legends can shed some light on my issue.

Starting from a fresh server reboot with a zero-size TempDB:

  1. An action happens where the TempDB needs 100mb, so grows by 100mb. OK
  2. Another action happens where it needs more, and it grows by 110mb. Eh?? 10% growth should be 10mb, not 110mb

This continues on every time it needs to grow, which is small at first but eventually it's growing by gigabytes every time. We quickly chewed up all 150gb of space on the drive and our ERP system collapsed in a heap and caused an outage while the server was restarted, because it grew by like 4+gb

Surely this can't be normal?

Some background information on the server:

Autogrowth Settings

As far as I can tell, these are the only settings I can change? So I can understand the 100mb -> 110mb is a 10% jump, but the growth is cumulative. In other words, the files size is now 210mb. When it grows again, it'll be 331mb. So you can see how I can quickly burn through disk space.

DB Growth Log

Am I interperting this incorrectly? Shouldn't 10% growth to a 100mb file result in a 110mb file, not 210?

For some extra background, this is a single SQL server instance with around 70 databases, 20 of which are "active" as they are used by our ERP system. The others or historical. Each business division gets it's own database, so we run the single ERP system but it connects to 20 different databas, so essentially has 20 full database installations of itself on the server.

Edit (for Craig's response) - To clarify the growth and the use of MB and GB, this is the growth history from the logs:

GrowthInMB 125 137 151 166 183 201 221 243 267 294 324 356 392 431 474 522 574 631 695 764 841 925 1017 1119 1231 1354 1489 1638 1802 1982 2181 2399 2639 2903 3193 3512 3864 4250 4675 5143

So at the end of this, my tempdb is 60GB, not 4.4GB. If the growth of 10% each time, it should be 10% larger than the previous, so 100MB grows to 110MB, but 10% growth is resulting in 100MB + 110MB to 210MB.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Mark
  • 11
  • 4
  • 1
    Looks like your interpretation is perhaps not quite right - your TempDB does seem to be growing in accordance with the auto-grow settings. (Starts out with a 1GB file, and is growing by 10% each time, it seems to me). In your question, you're variously sometimes saying MB and sometimes saying GB - so it's seems you're slightly confused on those numbers. – Craig Feb 06 '23 at 00:38
  • On the more important question - "Is it normal for TempDB to grow very large in a short amount of time?", the answer is Yes, and No. TempDB is used by SQL Server for any operations that it is asked to do involving temporary tables - and SQL will use as much of TempDB as it needs to perform those operations. So - in a real, practical sense, if your SQL Server is being asked to do lots of "temporary" operations, that will explain the growth in TempDB. – Craig Feb 06 '23 at 00:41
  • 1
    From the perspective of normal day-to-day operations, however, it would not be considered normal for TempDB to grow in this fashion (and certainly not to consume all available disk space - which has only happened because the auto-growth settings allow unlimited growth). If you are not an experienced DBA, then I would suggest you are going to have some difficulties diagnosing what might be causing the extraordinary use of TempDB. Perhaps you have access to technical database resources from the ERP company (or whatever partner assists you)? – Craig Feb 06 '23 at 00:43
  • 1
    Just throwing potential issues out there - is the recovery model of tempdb 'simple' or something else? If it's something else and you're not managing the logs/backups, it potentially could cause this. Also, consider reviewing what Brent Ozar says about tempdb setup e.g., https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/ - basically, set your tempdb to take all the space you want it to have. He then suggests another 1MB file on a different drive (with space) with autogrowth on and notifications when it grows. Seems like a sane idea. – seanb Feb 06 '23 at 00:51
  • Thanks, I'll check out the link and see if it helps. The DB is on Simple recovery - at this point, I'm assuming the previous DBA knew what he was doing. I've no experience in managing databases beyond what I've managed to learn from here or Google. Basically, I was dropped into it while we recruit for the replacement position and get someone who knows what they're doing. I'm just trying to keep the boat from sinking until then. The multiple tempdb files sound like a workable solution and would stop everything from grinding to a halt until we can resize the first. – Mark Feb 06 '23 at 01:06
  • There are valid reasons to use tempdb. There are lots of "bad" reasons things can use tempdb. If you have an app that is poorly designed or misbehaving, you can see unconstrained tempdb growth. You should try getting a handle on what is using space in tempdb. This is a reasonable blog post on the subject: https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/ – Conor Cunningham MSFT Feb 06 '23 at 14:32
  • Looking at your growth numbers, is it possible that what you're looking at is the size after the growth has occurred? E. g. between 137 - 125 = 12 which is roughly 10% of the starting size during that growth event. Additionally, if you're concerned about the growth being a percentage, you're welcome to change that setting to a fixed amount. – Ben Thul Feb 06 '23 at 15:45
  • @BenThul - it's definitely how much it grew by, and not the size after growth. If was size after growth, my tempdb would be 5gb and I wouldn't have an issue. After that last record the combined total was around ~60gb or so. – Mark Feb 06 '23 at 22:11

0 Answers0