8

I have a TempDB on SQL 2008 that has gotten very large (>40gb) and I want to shrink it down. I have used the dbcc shrinkdatabase, dbcc shrinkfile and the shrink command through Management Studio.

I get the following error:

Page 1:4573184 could not be moved because it is a work table page.

I have been able to reclaim some space to get me out of danger by running DBCC FREEPROCCACHE and re running one of the shrink routines, but obviously this isn't ideal and will likely only buy me a bit time.

I have run DBCC OpenTran and there is nothing that is hanging out there.

Everywhere I have read on internet comes down to recycling SQL Server...surely there has to be a better way...anyone?

Thanks,

Tom

7

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:

  1. Fix the process that is making tempdb grow abnormally large in the first place.
  2. 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:
  • 1
    SELECT * FROM sys.dm_tran_active_transactions WHERE name = N'worktable'; returns 6 rows all from the same date (7/10/14). Does the transaction_id represent the SPID? – user45117 Aug 6 '14 at 17:39
  • No, transaction_id is not SPID. If the transactions are really active (and they are not system transactions), you should be able to match to a session_id in sys.dm_tran_session_transactions. In any case, are you sure this is the message you are getting from DBCC SHRINKFILE? What about ALTER DATABASE ... MODIFY FILE? Also I am not sure I understand why clearing the procedure cache got you out of trouble; procedure cache is in memory, not in tempdb... – Aaron Bertrand Aug 6 '14 at 17:42
  • The message came from DBCC SHRINKDATABASE. I have not tried the ALTER DATABASE...MODIFY FILE...that will be next. Clearing the procedure cache allowed the tempdb to be shrunk by approximately 10% and gave me back 4gb of drive space. I was not able to link those transaction_id from sys.dm_tran_active_transactions to anything in sys.dm_tran_session_transactions – user45117 Aug 6 '14 at 17:56
  • 1
    I think clearing the procedure cache was less related to "allowing" tempdb to shrink than you think. Coincidences happen all the time. – Aaron Bertrand Aug 6 '14 at 18:02
0

Everywhere I have read on internet comes down to recycling SQL Server...surely there has to be a better way...anyone?

No this is a temporary solution. I guess you posted same question before could you please tell what is total size of database you have in your SQL Server instance. Size of tempdb depends on how much your queries are using it. It cannot grow by its own unless you use it. Links shared by Aron would help but you need to tune queries if they are heavily using temdbb or may be its the default requirement of your environment. I have seen few env. where 200 G of tempdb was acceptable because queries required that much amount of tempdb space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.