How to "lose weight" on tempdb in SQL Server

SQL Server automatically creates a database named tempdb for use as a workspace, when you create a temporary table in a stored procedure, such as (Create TABLE #MyTemp), regardless of which database you are using, The SQL database engine will create this table in the tempdb database.

Also, when you sort a large result set, such as using an order by or group by or union or executing a nested SELECT, the SQL database engine creates a work table in tempdb if the amount of data exceeds the system memory capacity. The SQL database engine also uses tempdb when you run DBCC REINDEX or add a cluster sequence to an existing table. In fact, any ALTER TABLE command for a large table will consume a large amount of disk space in tempdb.

Ideally, SQL will automatically clean up and destroy the temporary tables after the specified actions are completed, but many problems can lead to errors. For example, if your code creates a transaction that is not able to execute or re-run, the orphan objects will be left in tempdb. Also, when you run DBCC check on a large database, it consumes a lot of space, and you tend to find that tempdb is a lot bigger than you think, and you even get an error message that SQL is about to use up disk space.

There are a number of ways you can fix this, but in the long run you will need to perform other steps to ensure normal use.

The easiest way to "lose weight" for tempdb is to shut down the SQL database engine and reboot. But in an important task, it may be difficult to do so, and if you are already in an unbearable state, then my advice is to tell your boss the bad news and start the operation.

If you are lucky to have another disk that you can use to place tempdb, you can do the following:

USE master
GO
ALTER DATABASE tempdb modify file (name = tempdev, filename ='NewDrive:Path empdb.mdf')
GO
ALTER DATABASE tempdb modify file (name = templog, filename ='NewDrive:Path emplog.ldf')
GO

There are three other properties for tempdb that should be checked: automatic growth tags, initial size, and recovery mode, and here are some tips for these properties:

Auto Grow Tags: Remember to set this tag to true.

Initial Size: The initial size of tempdb is based on commonly used workloads, and if there are many users using group by, an order by, or aggregating operations on large tables, your common workloads will be quite large. If the server is offline, you may want to check that the log files are on the same disk as the data files, and if so, you should transfer them to a new disk, you only need to indicate the appropriate database and use the same command.

Recovery mode: Setting the recovery mode to true means that SQL automatically truncates the log files of tempdb (after each table is used) to find the recovery model used by tempdb, you can use the following command:

SELECT databasepropertyex (' tempdb ', ' recovery ')

There are three options for the recovery model: simple, complete, or large amounts of records (bulk-logged), and if you want to change the settings, you can use the following command:

ALTER DATABASE tempdb SET RECOVERY Simple

These steps can optimize the tempdb used in your system, and in addition to resolving disk space problems, you will also find an increase in SQL Server system performance.