• Adam Thurgar

Shrinking tempdb


I was asked to assist in changing the tempdb configuration on a SQL Server to use multiple files and to shrink the size down from a single file of 90GB to eight files, 5GB each for a total size of 40GB.

Except tempdb wouldn't shrink!

Even though it was almost empty, it just wouldn't shrink.

Luckily I remembered to do a DBCC FREEPROCCACHE before trying to shrink again and then the shrink worked.

SQL Server internally uses tempdb for lot of things - one is obviously tied to query plans.

Many years ago I suggested to Microsoft SQL Server team that there should be two tempdb databases - one for SQL Server internal operations and the other for users.


5 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t