CODEWITHSUNDEEP
sql-servert-sqlssmstempdb
Mark
Mark

Reputation: 11

Is Exponential Growth for SQL Server TempDB Normal?

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.

Upvotes: 1

Views: 176

Answers (0)

Related Questions