Shrinkfile on a datafile is a single-threaded operation, reusing a small memory buffer.
So the Ninja hardware hasn't got an edge with the extra memory and the 80 cores.
Your local PC however has the benefit of local I/O latency (local disk, i.e. not having to make multiple trips to the SAN).
Note: this post might be useful too:
Issues with TempDB mdf file ever increasing
Unless you can figure out what process is using that work table (and can safely kill it), I'd have to agree with what your searches have already yielded: cycle the server and you should be able to shrink tempdb.
A different question has dealt with figuring this out for #temp tables; I don't know if it can be adapted for work tables:
Find which session is holding which temporary table
I've also blogged about it (again, for #temp tables):
http://sqlperformance.com/2014/05/t-sql-queries/dude-who-owns-that-temp-table
I doubt the work table is related to snapshot isolation / version store, but just in case:
Find transactions that are filling up the version store
Also, don't rely on DBCC OPENTRAN; - I've observed many scenarios where I know I have an active transaction but it doesn't show up there. And note that database context is important; the database where the transaction is active is not necessarily tempdb. What do you see here? Anything?
SELECT * FROM sys.dm_tran_active_transactions
WHERE name = N'worktable';
Once you've shrunk tempdb
Of course, this isn't a permanent solution. You're going to shrink tempdb, and then it's going to grow again. This can become very boring and tedious to play this game every time it happens. And if it is going to grow again, what are you going to do with that free space in the meantime? Lease it out and then evict people when tempdb needs it again? You need to either:
- Fix the process that is making tempdb grow abnormally large in the first place.
- Allocate enough space for tempdb so that it doesn't need to grow, and stop shrinking it (especially if only temporarily; this is just wasted work!).
A couple of other suggestions:
- Don't use
SHRINKDATABASE (which should be called auto-fragment) or the UI. Write specific, targeted SHRINKFILE commands to affect individual files.
- Consider using multiple files for tempdb (which you could spread out to different storage if/when possible), and consider trace flags 1117 (as long as this behavior will not affect your user databases too) and 1118.
- Some tips on minimizing tempdb utilization here:
Best Answer
Without having access to the source code for SQL Server, we can only guess. So,
Edit: 0 means shrink to initial size as per page header. My speculation that it means dummy, below, is incorrect.
SET SPECULATION MODE ON
0 makes no sense. If you want to get rid of the file, you have the EMPTYFILE option. I.e., why would you want to have a file which is 0 MB in size.
I just did a test, and indeed 0 means it doesn't do anything. We could argue that an error messages would be better: "0 MB is not valid file size.", or something like that. But apparently MS decided to leave 0 as a valid valid for the command and implementing it as a dummy.
SET SPECULATION MODE OFF
I suggest you file a request with MS to either document that 0 means "dummy", or behavior change so we'll get an error message (less likely to happen).