![]() This method requires you to restart SQL Server.Īt a command prompt, start the instance in minimum configuration mode. To remove additional files in tempdb, use the ALTER DATABASE command with the REMOVE FILE option. However, they are empty and can be removed. All tempdb files are re-created during startup. If more files are added to tempdb, you can shrink them after you restart SQL Server as a service. ![]() This command operates only on the default tempdb logical files tempdev and templog. Therefore, it has no transactions to roll forward or to roll back.įor more information about managing and monitoring tempdb, see Capacity planning and Monitor tempdb use. Additionally, you don't have to log information to redo any transactions because tempdb is re-created every time that you restart SQL Server. This feature increases the performance of INSERT statements in tempdb. SQL Server records only enough information in the tempdb transaction log to roll back a transaction, but not to redo transactions during database recovery. Therefore, in order to make sure that a shrink of tempdb succeeds, we recommend that you do this while the server is in single-user mode or when you have stopped all tempdb activity. However, you may encounter other errors such as blocking, deadlocks, and so on, that can prevent shrink from completing. You can shrink tempdb while tempdb activity is ongoing. Therefore, unless you have to use different values or obtain immediate resolution to a large tempdb database, you can wait for the next restart of the SQL Server service for the size to decrease. The configured size is the last explicit size that was set by using a file size changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the DBCC SHRINKFILE or DBCC SHRINKDATABASE statements. When SQL Server starts, tempdb is re-created by using a copy of the model database, and tempdb is reset to its last configured size. Larger tempdb database sizes won't adversely affect the performance of SQL Server. Therefore, this database may unexpectedly grow in time to a size larger than the desired size. Shrink database files through a graphical user interface.īy default, the tempdb database is configured to autogrow as needed. Gives complete control on the size of the default tempdb files ( tempdev and templog). If you want to use SQL Server Management Studio, follow the instructions in Shrink a database. The first three options are described in this article. You can use any of the following methods to alter the size of tempdb. This article discusses various methods that you can use to shrink the tempdb database in SQL Server. Applies to: SQL Server Azure SQL Managed Instance ![]()
0 Comments
Leave a Reply. |