-1

After deploying an SSIS solution I found that tempdb filled up all the remaining hardrive space (250GB). I was then able to shrink it to about 70GB, where it has stayed even after a computer restart (with the SSIS jobs disabled).

Looking at this query:

USE tempdb;
GO

SELECT
    t.name AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
    SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB

FROM tempdb.sys.tables t
INNER JOIN tempdb.sys.indexes i ON t.object_id = i.object_id
INNER JOIN tempdb.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN tempdb.sys.allocation_units a ON p.partition_id = a.container_id

GROUP BY
    t.name,
    p.rows

I get the following result:

+--------------------------------------+-----------+--------------+-------------+
|              TableName               | RowCounts | TotalSpaceMB | UsedSpaceMB |
+--------------------------------------+-----------+--------------+-------------+
| #A037DC2D                            |         0 |            0 |           0 |
| #B0F5989D                            |         0 |            0 |           0 |
| #B4C62981                            |         0 |            0 |           0 |
| #B6AE71F3                            |         0 |            0 |           0 |
| #B896BA65                            |         0 |            0 |           0 |
| #B98ADE9E                            |         0 |            0 |           0 |
| #BB732710                            |         0 |            0 |           0 |
| #BC674B49                            |         0 |            0 |           0 |
| #BE4F93BB                            |         0 |            0 |           0 |
| #BF43B7F4                            |         0 |            0 |           0 |
| #SomeAppData__<...>___000000000029   |    134066 |            4 |           4 |
| #OtherAppData__<...>____000000000028 |    604524 |          123 |          44 |
+--------------------------------------+-----------+--------------+-------------+

We add data to tempdb, i think just by inserting into #someName, and then drop #someName when it's not needed anymore.

Looking at THIS thread, i want to know if tempdb size can be related to configuration?

The answer I'm looking at is:

TempDB can grow to such levels but shouldn't remain at this level. All temo tables are dropped at the end of the session that created it If your temp table is not having any tables in it then chances are the db file size settings are incorrect , check for SIze , MAX SIZE and esp FILEGROWTH

Alternatively, could the database size of the tempdb .mdf file be incorrect (though this would mean that the data in sys.allocation_units is also incorrect)? The space is clearly accounted for when looking at the free space on C:\ (which is why I'm worried!).

  • 1
    Tempdb is recreated at every restart with the size it was last configured. Not with the size it aquired while server was working, but with the last configured. So what is your question? – sepupic Jul 19 '17 at 12:42
  • How do you configure the size of tempdb? I would like to change the configuration – Zach Smith Jul 19 '17 at 12:45
  • also. the table output above shows used space of 48MB, and total space of 127MB. why would the tempdb space on disk reflect as 70GB? - which is obviously very different – Zach Smith Jul 19 '17 at 12:47
  • You can increase the initial size the same way you do it with other db files; For decrease you can try to shrink but you can do it without success, in that case you can restart the server in minimal configuration which will constrain the server use the default file size (1 or 3Mb, smth like this) – sepupic Jul 19 '17 at 12:51
  • 2
    You can imagine the initial size of tempdb ad the box volume. You've ordered a box with the capasity of 70lt (70Gb), the server was provided you with such a box. Then you put in it only few things that occupie almost no space (you've put in 2 newspapers), the box volume is the same, 70lt, space used 0,127lt, free space almost 70lt – sepupic Jul 19 '17 at 12:56
2

I found this article describing what was just said: How to shrink the tempdb database in SQL Server

I'll copy here the key moments:

  • The tempdb size is reset to the last configured size (that is, to the default size, or the last size that was set by using alter database) after each restart. Therefore, unless you have to use different values or obtain immediate respite, you do not have to use any of the procedures that are documented in this article. You can wait for the next restart of the SQL Server service for the size to decrease. Larger tempdb database sizes will not adversely affect the performance of SQL Server.
  • In SQL Server 2005 and later versions, shrinking the tempdb database is no different than shrinking a user database except for the fact that tempdb resets to its configured size after each restart of the instance of SQL Server.
  • It is safe to run shrink in tempdb while tempdb activity is ongoing. However, you may encounter other errors such as blocking, deadlocks, and so on that can prevent shrink from completing. Therefore, in order to make sure that a shrink of tempdb will succeed, we recommend that you do this while the server is in single-user mode or when you have stopped all tempdb activity.

The 3 methods how to change tempdb size are described there step by step:

  • Method 1: Use Transact-SQL commands: starting server in a minimum configuration mode that has a tempdb size of 1 MB for the data file and 0.5 MB for the log file and using ALTER DATABASE tempdb MODIFY FILE

  • Method 2: Use the DBCC SHRINKDATABASE command

  • Method 3: Use the DBCC SHRINKFILE command
  • Thanks. I saw that - the shrink database / files commands don't achieve anything (I did this from SSMS, not T-SQL though) – Zach Smith Jul 19 '17 at 13:29
  • That is the same thing. SSMS is using the same T-SQL commands behind the scenes – sepupic Jul 19 '17 at 13:39

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.