In addition, when you sort a large result set, such as using ORDER BY or GROUP BY or UNION or performing nested SELECT, if the amount of data exceeds the system memory capacity, the SQL database engine will create a worksheet in tempdb. The SQL database engine will also use tempdb when you run DBCC to re-index or add a clustering sequence to an existing table. In fact, any ALTER TABLE command for large tables will consume a lot of disk space in tempdb.
Ideally, SQL will automatically clean and destroy these temporary tables after the specified operation is completed, but many problems will lead to errors. For example, if your code creates a transaction but fails to execute or rerun it, these orphaned objects will remain in tempdb. In addition, when running DBCC check on a large database, it will consume a lot of space. You often find that tempdb is much larger than expected, and even get an error message that SQL is about to run out of disk space.
There are many ways to correct this situation, but in the long run, you need to take other steps to ensure normal use.
The easiest way to "lose weight" for tempdb is to shut down and restart the SQL database engine, but in important tasks, it may be very difficult to do so; On the other hand, if you are in an unbearable state, then my suggestion is to tell your boss the bad news and start the operation.
If you are lucky enough to have another disk for tempdb, you can do the following:
Use master
go to
ALTER DATABASE tempdb modification file (name = tempdev, filename =' newdrive: pathempdb.mdf').
go to
ALTER DATABASE tempdb modification file (name = templog, filename =' newdrive: pathemployment.ldf').
go to
Three other properties of tempdb should be checked: automatic growth tag, initial size and recovery mode. Here are some tips about these properties:
Auto-growth flag: Remember to set this flag to True.
Initial size: The initial size of tempdb should be set according to the common workload. If many users are using GROUP BY, ORDER BY or aggregating large tables, your public workload will be very large. If the server is offline, you may need to check whether the log file and data file are on the same disk. If so, you should transfer them to a new disk. You just need to point out the corresponding database and use the same command.
Recovery mode: Setting the recovery mode to True means that SQL will automatically truncate the log file of tempdb (after using each table). To find out the recovery mode used by tempdb, you can use the following command:
SELECT DATABASEPROPERTYEX(' tempdb '，' recovery ')
There are three options for the recovery model: simple, complete or bulk-logged. If you want to change the settings, you can use the following command:
Changing the database Temporary database set is easy to restore.
These steps can optimize tempdb used in the system. In addition to solving the problem of disk space, you will also find the improvement of SQL Server system performance.