SQL Server BDC Hints and Tips:​​ TEMPDB​​ Disk Usage

Published Feb 09 2022 07:30 AM 910 Views
Microsoft
Move from: bobsql.com

 

I was troubleshooting​​ a​​ master-0 pod eviction​​ due to​​ disk pressure. ​​ 

  • The pod was​​ shutdown

  • Ephemeral space reclaimed (85% to 73%) –​​ Deleted unused images from the node​​ (I.E. docker image purge …)

  • The pod was started​​ and master-0 came back online

Right after the restart I saw the disk space usage drop to​​ 38% but​​ found​​ no events in the journal or other logs indicating how the additional space was​​ acquired. ​​​​ Looking at the disk metrics I was able to map the time​​ of​​ the disk​​ space increase to the startup of SQL Server, specifically TEMPDB.

 

The TEMPDB had grown large,​​ prior to the restart​​ (auto grow no limit), consuming disk space on the master-0 persistent volume.  ​​​​ Once the 80% disk space usage threshold was crossed the pod​​ was evicted​​ followed by the​​ cleanup and​​ restart attempt. ​​ Luckily, enough space​​ was​​ reclaimed by deleting the unused images​​ allowing​​ the​​ master-0 pod​​ to restart. ​​ The master-0​​ pod restarted​​ (SQL Server restarted) and TEMPDB was recreated​​ using​​ the default file sizes. ​​ The TEMPDB​​ recreation caused​​ disk space usage​​ to change​​ from 73% to 38%.

 

If​​ enough​​ space could not be reclaimed​​ as part of the eviction resolution,​​ the pod generally goes into CrashLoopBackOff​​ and/or remains in evicted state.  ​​​​ When disk space used remains above the eviction threshold (80%) Kubernetes is unable to restart the pod​​ and space reclamation​​ requires intervention to correct. ​​ You​​ commonly must​​ log into the node and free disk space or add additional disk space resources for the persistent volume​​ to allow the pod to restart.

 

Recommendation
Be sure to set file size limits on your TEMPDB to avoid unwanted disk space consumption leading to pod eviction.

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3143833%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ESQL%20Server%20BDC%20Hints%20and%20Tips%3A%20TEMPDB%20Disk%20Usage%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3143833%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CH6%20id%3D%22%5C%26quot%3Btoc-hId-1764461375%5C%26quot%3B%22%20id%3D%22toc-hId-1764461470%22%3EMove%20from%3A%20bobsql.com%26lt%3B%5C%2FH6%26gt%3B%5Cn%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%3CSPAN%3EI%20was%20troubleshooting%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ea%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Emaster-0%20pod%20eviction%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Edue%20to%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Edisk%20pressure.%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3CUL%20class%3D%22%5C%26quot%3BLFO2%5C%26quot%3B%22%3E%5Cn%3CLI%3E%3CP%20class%3D%22%5C%26quot%3BP5%5C%26quot%3B%22%3E%3CSPAN%3EThe%20pod%20was%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eshutdown%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FLI%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%20class%3D%22%5C%26quot%3BP6%5C%26quot%3B%22%3E%3CSPAN%3EEphemeral%20space%20reclaimed%20(85%25%20to%2073%25)%20%E2%80%93%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3EDeleted%20unused%20images%20from%20the%20node%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3E(%26lt%3B%5C%2FSPAN%26gt%3B%3CSPAN%20class%3D%22%5C%26quot%3BT7%5C%26quot%3B%22%3EI.E.%20docker%20image%20purge%20%E2%80%A6)%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FLI%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%20class%3D%22%5C%26quot%3BP8%5C%26quot%3B%22%3E%3CSPAN%3EThe%20pod%20was%20started%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eand%20master-0%20came%20back%20online%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%26lt%3B%5C%2FLI%26gt%3B%5Cn%26lt%3B%5C%2FUL%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%3CSPAN%3ERight%20after%20the%20restart%20I%20saw%20the%20disk%20space%20usage%20drop%20to%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3E38%25%20but%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Efound%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eno%20events%20in%20the%20journal%20or%20other%20logs%20indicating%20how%20the%20additional%20space%20was%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eacquired.%26lt%3B%5C%2FSPAN%26gt%3B%26nbsp%3B%20%26nbsp%3B%3CSPAN%3ELooking%20at%20the%20disk%20metrics%20I%20was%20able%20to%20map%20the%20time%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eof%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ethe%20disk%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Espace%20increase%20to%20the%20startup%20of%20SQL%20Server%2C%20specifically%20TEMPDB.%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%3CSPAN%3EThe%20TEMPDB%20had%20grown%20large%2C%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eprior%20to%20the%20restart%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3E(auto%20grow%20no%20limit)%2C%20consuming%20disk%20space%20on%20the%20master-0%20persistent%20volume.%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%26nbsp%3B%20%26nbsp%3B%3CSPAN%3EOnce%20the%2080%25%20disk%20space%20usage%20threshold%20was%20crossed%20the%20pod%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ewas%20evicted%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Efollowed%20by%20the%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ecleanup%20and%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Erestart%20attempt.%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3ELuckily%2C%20enough%20space%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ewas%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ereclaimed%20by%20deleting%20the%20unused%20images%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eallowing%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ethe%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Emaster-0%20pod%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eto%20restart.%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3EThe%20master-0%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Epod%20restarted%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3E(SQL%20Server%20restarted)%20and%20TEMPDB%20was%20recreated%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eusing%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ethe%20default%20file%20sizes.%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3EThe%20TEMPDB%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Erecreation%20caused%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Edisk%20space%20usage%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eto%20change%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Efrom%2073%25%20to%2038%25.%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%3CSPAN%3EIf%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eenough%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Espace%20could%20not%20be%20reclaimed%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eas%20part%20of%20the%20eviction%20resolution%2C%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ethe%20pod%20generally%20goes%20into%20CrashLoopBackOff%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eand%2For%20remains%20in%20evicted%20state.%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%26nbsp%3B%20%26nbsp%3B%3CSPAN%3EWhen%20disk%20space%20used%20remains%20above%20the%20eviction%20threshold%20(80%25)%20Kubernetes%20is%20unable%20to%20restart%20the%20pod%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eand%20space%20reclamation%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Erequires%20intervention%20to%20correct.%26nbsp%3B%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3EYou%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Ecommonly%20must%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Elog%20into%20the%20node%20and%20free%20disk%20space%20or%20add%20additional%20disk%20space%20resources%20for%20the%20persistent%20volume%26lt%3B%5C%2FSPAN%26gt%3B%20%26nbsp%3B%3CSPAN%3Eto%20allow%20the%20pod%20to%20restart.%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%5Cn%3C%2FP%3E%3CP%20class%3D%22%5C%26quot%3BNormal%5C%26quot%3B%22%3E%3CU%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%5C%26quot%3BT9%5C%26quot%3B%22%3ERecommendation%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FU%26gt%3B%3CSPAN%20class%3D%22%5C%26quot%3BT10%5C%26quot%3B%22%3E%3CBR%20%2F%3E%26lt%3B%5C%2FSPAN%26gt%3B%3CSPAN%3EBe%20sure%20to%20set%20file%20size%20limits%20on%20your%20TEMPDB%20to%20avoid%20unwanted%20disk%20space%20consumption%20leading%20to%20pod%20eviction.%26lt%3B%5C%2FSPAN%26gt%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-TEASER%20id%3D%22%5C%26quot%3Blingo-teaser-3143833%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3C%2FLINGO-TEASER%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3ESQL%20Server%20Big%20Data%20Cluster%2C%20tempdb%20disk%20usage.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-teaser%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3143833%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EBobSQL%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FH6%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Co-Authors
Version history
Last update:
‎Feb 09 2022 07:30 AM
Updated by: