sql-server add tag
James Jenkins
This questions comes from an answer posted at SE.

> Tempdb can grow immense if, for some reason, a nasty query. Is executed that eats up tempdb. Putting tempdb on a separate drive, which is the same storage under the cover, makes sure that your data or log volume won’t get full. If the tempdb volume gets full, you will receive an error message that tempdb is full, instead of your db possibly stopping because the data or log volume is full. [Source](https://dba.stackexchange.com/a/268828/21924)

If for some reason i.e. a low budget physical machines, giving the tempdb it's own drive is not practical, and if the performance trade off is acceptable. How do I keep a single nasty query from using all the disk space and bringing down all the databases?
Top Answer
Josh Darnell
Probably the simplest way would be to turn off autogrowth for all tempdb files, making sure to set them to a reasonable fixed-size based on your workload and available space.

This sets all 4 of my tempdb files, and the log file, to 1 GB each, and disables autogrowth:

```
USE [master]
GO

ALTER DATABASE [tempdb] MODIFY FILE (
    NAME = N'tempdev', SIZE = 1048576KB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (
    NAME = N'temp2',   SIZE = 1048576KB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (
    NAME = N'temp3',   SIZE = 1048576KB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (
    NAME = N'temp4',   SIZE = 1048576KB , FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (
    NAME = N'templog', SIZE = 1048576KB , FILEGROWTH = 0);
```

Running a query that would require more than the available tempdb space results in this error message for that query:

> Msg 1101, Level 17, State 1, Line 1
> Could not allocate a new page for database ‘tempdb’ because of insufficient disk space in filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.