This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Database Maintenance - tempdb large size and duplicates?

Hello all,

We recently started getting an error with our Database Maintenance: "Operation: Remove temporary system files. Target: Files". I'm seeing that in the T drive, there are multiple tempdb's. Two of these, tempdb and tempdb_mssql2, are created every night with the DB maintenance. These two started out at ~50 GB when I first noticed the issue, but have grown to be 75 GB over about two weeks. The other two are tempdb_mssql3 and 4, are only 9 MB each and are about two years old.

Our T drive is nearly full and we've already increased the size on it from 100 GB to 150, so I'm trying to get this under control.

The main things I'm wondering are:

1) Why are there multiple, possibly duplicate tempdb's/Is that standard?

2) What exactly does the error message mean by "remove temp system files". Which files? My understanding is tempdb is often in use, but I could find no reference to tempdb_mssql# files, so I'm thinking maybe those are the files that cannot be deleted during the database maintenance, but should be.

3) Why are two of the tempdb's very large? Both my googling and the size of the 3 and 4 tempdb's suggest these files should be in the realm of MBs, not gigs. But the two that are currently getting created or updated with every DB maintenace are 75 GB and seemingly growing at a rough rate of 1 GB a day.

I found multiple articles/forums and one of the common things seemed to be to have your DB Recovery set to simple and I ensured that was our case. I also checked the size of our DB and the Orion one (not sure about logs DB) is about 350 GB - Not outlandish I think and I also assume there isn't a standard ~5:1 DB size to temp DB ratio.

This seems possibly correlated with our upgrade to 2022.4. That occurred on Dec 13, and we noticed this issue Jan 17. Though our monitoring shows T didn't start growing on the SW SQL server until the first week of the new year, so I can't say the upgrade and this growth are super tightly correlated.

I already opened a case with TAC and they acknowledged this is a known bug with no fixes. I'm still trying to figure out what I can do to work around and free up space on T, so I figured I would make this post and see if any of you Thwackers were familiar with this.

* I will also add this is my first time posting on Thwack, so let me know if this is in the wrong forum and I will gladly delete and repost.

Thanks,
Luke