To Cap your TempDB or not to Cap your TempDB?

by Daniel Williams   Last Updated December 11, 2017 20:06 PM

I currently have tempdb capped at roughly 50 gigs across 2 tempdb's:

 use master
--This first line modifies the MAIN database file. Size you can set in GB and Growth to either 10mb for slow growth or 10% for fast growth
ALTER DATABASE [tempdb] modify FILE (NAME = N'tempdev', FILENAME = N'E:\SQL\Data\tempdb.mdf' , SIZE = 25GB , FILEGROWTH = 0);

--This second line modifies the SECONDARY file. Size you can set in GB and Growth to either 10mb for slow growth or 10% for fast growth
ALTER DATABASE [tempdb] modify FILE (NAME = N'tempdev2', FILENAME = N'E:\SQL\Data\tempdb2.ndf' , SIZE = 25GB , FILEGROWTH = 0);

I know for a fact that, prior to this, some of my colleagues have run queries that grow the tempdb to 80gb+.

I'm wondering what will happen if they try to run a query that would NORMALLY grow the tempdb to crazy size but I have it capped. Will they get an error? Will it break everything? Will the database just overwrite itself from the oldest used data and everything will be hunky dory?

I'm looking to test this myself but I don't have an adequate testing environment. The place I WOULD test it on is only semi-prod but if there was a chance that it would break the world, I'd much rather not take it.



Related Questions


High write latancy in temp db

Updated November 04, 2016 09:02 AM

Moving TempDB data and log files

Updated June 19, 2015 23:02 PM

IDERA suggests to alter procedures in tempdb

Updated May 16, 2017 21:06 PM


Is IFI compatible with tempdb growth?

Updated June 01, 2016 08:02 AM