TempDB is a system database in SQL Server that is used to store temporary objects such as temporary tables, table variables, and cursors. It is crucial for the performance of your SQL Server instance to properly manage the size of the TempDB database. In this blog post, we will discuss a common issue related to TempDB size and provide a solution to resolve it.
The Problem
One of our clients recently encountered an issue with their TempDB database. They noticed that the TempDB files were excessively large and unevenly sized. Even when they attempted to shrink the database, it did not have any effect. The following error message was displayed in SQL Server Management Studio:
DBCC SHRINKFILE (1,1024)
DBCC SHRINKFILE: Page 1: 26423878 could not be moved because it is a work table page.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Upon further investigation, we discovered that the page number 26423878 corresponded to a size of approximately 201 GB. This was the size that the shrink operation was stuck on, even after restarting SQL Server.
The Solution
Before implementing any changes on a production server, it is essential to test them on a development server. The following steps outline the solution to the TempDB size issue:
- Stop the SQL Server service.
- Start SQL Server in minimal mode from the command prompt using the following command:
NET START MSSQLSERVER /f /mSQLCMD - Connect to SQL Server using SQLCMD.
- For each TempDB data file, execute the following command to set their initial size to 500 MB and file growth to 500 MB increments:
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 500MB, FILEGROWTH = 500MB) - Repeat the previous step for every TempDB data file.
- Verify that the initial size has taken effect by executing the following query:
SELECT name, size * 8.0 / 1024 AS 'Initial Size in MB' FROM master.sys.sysaltfiles WHERE dbid = 2 - Once the size has been reduced to the expected size, stop the SQL service using the command:
NET STOP MSSQLSERVER - Start the SQL service normally.
After following these steps, the TempDB size should be successfully reduced. It is important to note that the command MSSQLSERVER in the above commands is for the default instance. If you have a named instance, it would be MSSQL$InstanceName.
Conclusion
Managing the size of the TempDB database is crucial for maintaining optimal performance in SQL Server. In this blog post, we discussed a common issue related to TempDB size and provided a step-by-step solution to resolve it. It is important to remember that shrinking a database is not a recommended process as it can lead to increased fragmentation and reduced performance. For more information on this topic, we recommend reading our article on why shrinking a database is bad for performance.