Stop trying to shrink tempdb files
Tags: sql, sqlserver, database
Published: 2020-05-05 03:00:15

---
title: Stop trying to shrink tempdb files
published: true
date: 2020-04-28 13:00:24 UTC
tags: SQL, sqlserver, database
cover_image: 
canonical_url: https://am2.co/2020/04/stop-trying-to-shrink-tempdb/
---

## Stop shrinking your tempdb data files

I recently wrote about [growing, shrinking, and removing tempdb files](https://am2.co/2020/04/fixing-tempdb/). In that article I explained that SQL Server won’t move a page that contains an internal worktable object, and thus trying to shrink tempdb files can be futile. Today, I’m going to explain how attempting to shrink tempdb files can actually be harmful.

[![](http://am2.co/wp-content/uploads/black-and-white-cat-1317844-267x300.jpg)](https://ShouldIShrinkMyDatabase.com)<figcaption id="caption-attachment-3090">Every time someone shrinks a database, a kitten dies.</figcaption>



### How’s tempdb work again?

First, lets recall that when SQL Server starts, it will ensure that all datafiles are sized to match the metadata in `sys.master_files`. Let’s take a look at what that metadata looks like for tempdb on my laptop. We’ll look at both `master.sys.master_files` and `tempdb.sys.database_files`:

```sql
--Check sys.master_files in master
USE master
GO
SELECT DataFileName = mf.name, 
       FileSizeMB  = mf.size*8/1024
FROM sys.master_files AS mf
WHERE mf.database_id = db_id('tempdb')
ORDER BY mf.type, file_id;
GO
--Check sys.database_files in tempdb
USE tempdb
GO
SELECT DataFileName = dbf.name, 
       FileSizeMB  = dbf.size*8/1024
FROM sys.database_files AS dbf
ORDER BY dbf.type, dbf.file_id;
```

[![](http://am2.co/wp-content/uploads/tempdb-start-1024x786.png)](http://am2.co/wp-content/uploads/tempdb-start.png)

I’ve got 8 data files, each 8MB, and one log file that is 12MB. Cool–that’s a crummy configuration for a server, but just fine for this laptop. Also, we notice that it’s the same in both places.

Next, I’ll run some stuff on my laptop to generate a workload that will create some tempdb objects and force those data files to auto-grow. Let’s re-run those same two queries above, and look at the results:  
[![](http://am2.co/wp-content/uploads/tempdb-2-1024x785.png)](http://am2.co/wp-content/uploads/tempdb-2.png)

Notice how `sys.master_files` still shows the _configured starting size_, while `sys.database_files` shows the _current size_. For most databases, these two queries would return the same results. One of the exceptions is tempdb–where autogrowth is **not** tracked in `sys.master_files`. This is done to allow that metadata to remain constant so that SQL Server has the necessary information to “reset” tempdb to its configured size on restart.

Well, what if I don’t want to wait for a restart? Maybe I can just shrink the data files a wee bit now, because I need some space back on the drive. That’s OK to do, right?

I’ve frequently seen DBAs try to shrink tempdb files as a response to disk space alerts on the tempdb disk. In [my recent blog post](https://am2.co/2020/04/fixing-tempdb/), I discussed how tempdb shrink operations often fail with an error like `Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle`. Let’s put aside that complication, and assume that maybe I’m able to get the shrink to complete.

### I really want to shrink some tempdb files!

What I’ve seen done (and a younger Andy did once upon a time) was think something like this:  
_“I just need a little space back to get below the alert threshold, so I try to shrink just a little bit of space off every file!”_

```sql
USE tempdb
DBCC SHRINKFILE (tempdev,50);
DBCC SHRINKFILE (temp2,50);
DBCC SHRINKFILE (temp3,50);
DBCC SHRINKFILE (temp4,50);
DBCC SHRINKFILE (temp5,50);
DBCC SHRINKFILE (temp6,50);
DBCC SHRINKFILE (temp7,50);
DBCC SHRINKFILE (temp8,50);
```

Or maybe Younger Andy thought, _“But I really want to get space back. What if I shrink just one file and I can shrink it really small!”_

```sql
USE tempdb
DBCC SHRINKFILE (temp2,5);
```

On the next reboot, it’ll all resize back to the way it should be, right?

### NO! Younger Andy, you just screwed up tempdb’s metadata!

Remember these queries from the start of the blogs post? Let’s run them again:

```sql
--Check sys.master_files in master
USE master
GO
SELECT DataFileName = mf.name, 
       FileSizeMB  = mf.size*8/1024
FROM sys.master_files AS mf
WHERE mf.database_id = db_id('tempdb')
ORDER BY mf.type, file_id;
GO
--Check sys.database_files in tempdb
USE tempdb
GO
SELECT DataFileName = dbf.name, 
       FileSizeMB  = dbf.size*8/1024
FROM sys.database_files AS dbf
ORDER BY dbf.type, dbf.file_id;
```

I ran a `SHRINKFILE` to set every file to 50MB… then that one `temp2` file to 5MB. Except, file sizes are all over the place. To make it worse, the “restart metadata” in `sys.master_files` has been reset to wonky sizes as well. The only file I didn’t try to shrink is the log file–and that’s the only file that didn’t have the `sys.master_files` metadata mangled.

[![](http://am2.co/wp-content/uploads/tempdb-3-1024x784.png)](http://am2.co/wp-content/uploads/tempdb-3.png)

Any time you manually grow or shrink a tempdb file, the “restart metadata” in `sys.master_files` gets adjusted accordingly. If the data file can’t shrink to the requested size, then the metadata is set to the size the data file was actually resized to, not the requested size.

## Then what the heck are you supposed to do?

To start, just stop trying to shrink tempdb data files.

But how do you eliminate the need to shrink tempdb files?

- Use a dedicated disk for tempdb. 
- Let the data files fill the tempdb disk. 
- Turn off your space alerting _for that one disk_, and be comfortable that the disk is 100% full.
- Instead monitor the tempdb database itself for internal free space on log & data files.
- Manage the size of your tempdb data & log files so that the initial size is large enough for normal operations.
- If you do shrink a tempdb file, check the `sys.master_files` metadata before & after to ensure you leave it in the ideal state. Use `ALTER DATABASE...MODIFY FILE` to repair the metadata for the next restart

The post [Stop trying to shrink tempdb files](https://am2.co/2020/04/stop-trying-to-shrink-tempdb/) appeared first on [Andy M Mallon - AM²](https://am2.co).