Dedicated tempdb drive displays much more disk space usage than the actual size of the tempdb.mdf/ldf files

Question

Dedicated tempdb drive displays much more disk space usage than the actual size of the tempdb.mdf/ldf files

cmp119

SSChasing Mays

Points: 626
More actions
June 27, 2018 at 10:09 am

#352348

I need to ask a question referencing how SQL 2014 deals with the tempdb files and disk usage.
Our SQL Server has 128Gbs of RAM, with dual Xeon E5-2637 procs, (3) RAID10 arrays for the live and audit databases, logs files, and tempdb. The tempdb array uses SSD drives while the other (2) RAID10s use conventional 15K RPM drives.
I am not sure if the following is an issue, but its something that I have noticed over time. Also, Windows Server 2016 does not reveal any noticeable errors within the event logs, and SQL 2014 logs are relatively clean as well. Everything appears to be running fine, too.
I want to focus on drive (F:) as displayed below and also attached to this post just in case its not visibly clear. The tempdb.mdf and templog.ldf are relatively small, and do not normally grow above 1Gb. However, when viewing disk usage for this same drive it indicates its using 106Gbs. I've rebooted the server and both the mdf and ldf files shrink some, but the total drive usage remains the same and I as watch the overall disk usage it keeps growing without shrinking down to less than 1Gb. Please note, the only files/folders on this drive is the Temp folder. I have show hidden files and protected operating system files, and both the $Recyle.bin and System Volume Information folders are both empty.
So, if what I am describing above is normal, can you specify what exactly is consuming or at least the Windows OS believes is using much more disk space.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

cmp119 - Wednesday, June 27, 2018 10:09 AM
I need to ask a question referencing how SQL 2014 deals with the tempdb files and disk usage.
Our SQL Server has 128Gbs of RAM, with dual Xeon E5-2637 procs, (3) RAID10 arrays for the live and audit databases, logs files, and tempdb. The tempdb array uses SSD drives while the other (2) RAID10s use conventional 15K RPM drives.
I am not sure if the following is an issue, but its something that I have noticed over time. Also, Windows Server 2016 does not reveal any noticeable errors within the event logs, and SQL 2014 logs are relatively clean as well. Everything appears to be running fine, too.
I want to focus on drive (F:) as displayed below and also attached to this post just in case its not visibly clear. The tempdb.mdf and templog.ldf are relatively small, and do not normally grow above 1Gb. However, when viewing disk usage for this same drive it indicates its using 106Gbs. I've rebooted the server and both the mdf and ldf files shrink some, but the total drive usage remains the same and I as watch the overall disk usage it keeps growing without shrinking down to less than 1Gb. Please note, the only files/folders on this drive is the Temp folder. I have show hidden files and protected operating system files, and both the $Recyle.bin and System Volume Information folders are both empty.
So, if what I am describing above is normal, can you specify what exactly is consuming or at least the Windows OS believes is using much more disk space.

Not all files show in Windows File Explorer and/or it could also be volume shadow copies (aka shadow storage). If you open the command prompt and run as administrator, change to the drive and execute: vssadmin list shadowstorage
That will show you any shadow storage space.
Other things can also contribute so I usually use WinDirStat, run as administrator. It can show other things not displayed in File Explorer. It's a free tool:
WinDirStat

Sue

cmp119 SSChasing Mays Points: 626 More actions · Answer 2

I forgot all about that. It's strange that I Shadow Copies disables for all the drives. While reviewing the shadowstorage allocation for drive F:, I presume shadow copies is currently using 51.1Gbs of 98.7Gbs of allocated storage even though its disabled, right? This is odd since its disabled why would that be the case!?!?!?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

cmp119 - Wednesday, June 27, 2018 11:08 AM
I forgot all about that. It's strange that I Shadow Copies disables for all the drives. While reviewing the shadowstorage allocation for drive F:, I presume shadow copies is currently using 51.1Gbs of 98.7Gbs of allocated storage even though its disabled, right? This is odd since its disabled why would that be the case!?!?!?

There are some backup agents that can cause that. That's the one I've seen the most. Not sure what else can do that (system restore/protection but that's on desktop)
If you go to settings on the Shadow Copies tab, set the disk space usage to the lowest possible - I think it's around 300 MB or so. I've had to do an enable and then disable again afterwards for it to take effect before. I don't know if that's required or not but it's simple enough to do.

Sue

cmp119 SSChasing Mays Points: 626 More actions · Answer 4

Agree, I will adjust the shadow copy settings as suggested. Thanks all your help!!!!