How to "lose weight" for tempdb in SQL Server"

SQL Server automatically creates a database named tempdb for use as a workspace. When you CREATE a temporary TABLE during storage, for example (CREATE TABLE # MyTemp ), the SQL database engine creates the table in the tempdb database no matter which database you are using.

In addition, when you sort large result sets, such as using order by, group by, UNION, or executing a nested SELECT statement, if the data volume exceeds the system memory capacity, the SQL database engine creates a workbook in tempdb. When you run dbcc reindex or add a cluster sequence to an existing table, the SQL database engine also uses tempdb. In fact, any alter table command for large tables will eat a lot of disk space in tempdb.

Ideally, SQL will automatically clear and destroy these temporary tables after the specified operation is completed. However, many problems may cause errors. For example, if your code creates a transaction but fails to be executed or re-run, these orphan objects will be left in tempdb. In addition, when running dbcc check on large databases, it will consume a lot of space. You will often find that tempdb is much larger than expected, you may even receive an error message indicating that the SQL is about to run out of disk space.

You have many ways to correct this situation, but in the long run, you need to perform other steps to ensure normal use.

The easiest way to "lose weight" for tempdb is to disable the SQL database engine and restart it. However, this may be difficult for important tasks. On the other hand, if you are in an unbearable state, I suggest you tell your boss the bad news and start the operation.

If you are lucky to have another disk that can be used to store tempdb, you can perform the following operations:

USE master

GO

Alter database tempdb modify file (name = tempdev, filename = 'newdrive: \ Path \ tempdb. mdf ')

GO

Alter database tempdb modify file (name = templog, filename = 'newdrive: \ Path \ templog. ldf ')

GO

There are also three attributes for tempdb that should be checked: Automatic growth tag, initial size, and recovery mode. The following are tips for these attributes:

Automatic growth Tag:Remember to set this flag to True.

Initial size:The initial size of tempdb should be set based on common workloads. If many users use group by, order by, or aggregate large tables, your common workload will be quite large. If the server is offline, you may need to check whether the log files and data files are on the same disk. If so, you should transfer them to the new disk, you only need to specify the corresponding database and use the same command.

Recovery mode:Setting the recovery mode to True means that SQL can automatically intercept the log files in tempdb after each table is used. To find out the recovery mode used by tempdb, run the following command:

Select databasepropertyex ('tempdb', 'recovery ')

There are three options for recovery mode: simple, complete, or a large number of records bulk-logged). To change the settings, run the following command:

Alter database tempdb SET RECOVERY SIMPLE

These steps can optimize the tempdb used in your system. In addition to solving disk space problems, you will also find that the SQL Server system performance is improved.

(