0

So my tempdb log has 0% free when I checked my Disk Management in my windows server, I went to SSMS and checked the disk usage report GUI and it shows me that my transaction log is 99% free and the last autogrow stopped 2 days ago. This is production server FYI, so, should I restart my SQL server after hours? Shrink the templog? Just confused with what's going on having to see 2 different reports.

What should I do?

Disk Usage in SSMS

Templog 0% free in Disk Management

mustaccio
  • 25,896
  • 22
  • 57
  • 72
WhoIsNotActive
  • 13
  • 5
  • What are the Autogrowth / Maxsize settings for your tempdb's Log file? (You can find this in SSMS by going to Databases -> System Databases -> Right click tempdb and click Properties -> Files and look at the one with File Type = LOG). How big is the disk that your tempdb's Log file is on, and is it shared with anything else? – J.D. Oct 27 '22 at 15:58
  • "So my tempdb log has 0% free when I checked my Disk Management in my windows server" ---- as far as I remember Disk Management is not able to tell how full temp db log file is, it only can show how full the disk hosting the tempdb is – Aleksey Vitsko Oct 27 '22 at 16:07
  • @MichaelYimmer get the TempDBInfo stored procedure from here https://github.com/aleksey-vitsko/Database-Administrator-Tools (TempDB - TempDBInfo.sql). install (create in any user database) and run it on your SQL Server, and post what it shows to your question – Aleksey Vitsko Oct 27 '22 at 16:09
  • My tempdb autogrowth is unlimited, growing by 128MB...and it is in simple recovery model – WhoIsNotActive Oct 27 '22 at 17:33
  • You are confusing between allocated vs used space. See this Q&A. https://dba.stackexchange.com/questions/81746/difference-in-space-used – SqlWorldWide Oct 28 '22 at 11:47
  • @JohnK.N. Yes, very much! Ty :) – WhoIsNotActive Nov 04 '22 at 15:56

1 Answers1

2

When you check in "disk management", you are checking the free space on the disk. When you are checking in SQL, you are probably looking at the free space inside the SQL log file (.ldf).

A 10Gb log file could be use at 0%, it will still take 10Gb on disk.

Now, if the log file ended up being the actual size, it's because SQL needed it to be that big at some point. Shinking it will give you some free space on the disk, but if SQL needs the file to grow again, it will use that space again.

If something else uses the space in the midtime, then the tempdb log file will fill to 100% and you will get an error.

Seems to me like you are a bit "short" on space on this disk so I would make it bigger (if VM) or would look at adding another disk and move the tempdb log file over there.

Dominique Boucher
  • 3,118
  • 10
  • 22