tempdb

This blog is an outcome of an email communication with my client. The issue was that they were having big tempdb files. They had multiple files and of uneven size. As soon as they restart SQL, the initial size itself was huge and when they tried to shrink the TempDB database, it was not doing anything. Here was the message in SQL Server Management Studio query window.

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.

If we try to covert page number to location in MDF file, it was 26423878 * 8 KB = ~201 GB and that was the size on which they were stuck. We were seeing same page every time we try to shrink, even after restart of SQL Server. What was the option now?

 

WORKAROUND/SOLUTION

Please note that you try everything out on your development server before experimenting on the production server.

We need to restart SQL with a special switch -f which is used to start SQL with minimal configuration. Note: this needs downtime of SQL Server for some time as no one should connect to SQL.

  • Stop SQL Server service.
  • Start SQL Server in minimal mode from command prompt.
NET START MSSQLSERVER /f /mSQLCMD
  • Above command starts SQL Server in minimal configuration (f switch) and allows connections from the application named SQLCMD (mSQLCMD)
  • Connect to SQL using SQLCMD
  • In the SQLCMD connection, execute the following command for each one of the tempdb data files to set their initial size to 500 MB and file growth also to 500 MB increments.
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev',SIZE=500MB, FILEGROWTH = 500MB )
GO
  • Repeat this process for every tempdb data file.
  • Now verify the initial size has taken effect using the following query.
SELECT name ,size*8.0/1024  'Initial Size in MB'
FROM master.sys.sysaltfiles WHERE dbid = 2
  • Once size has come down to expected size, we need to stop SQL service NET STOP MSSQLSERVER
  • And now, start again normally.
  • Relax as tempdb size has been reduced.

Note: MSSQLSERVER in the above command is for the default instance. So, if you have named instance, it would be MSSQL$InstanceName

Have you ever seen such error during shrink? Did you find another way?

Please note that Shrinking Database is not recommended process and it can lead to slowest possible performance. You must read this article Shrinking Database is Bad – Increases Fragmentation – Reduces Performance.

Reference:Pinal Dave (http://blog.SQLAuthority.com) 

About the Author

Pinal Dave

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Notable Replies

  1. says:
    steve.trinh

    I tried this and I was not able to reduce the file sizes for the NDF files. When connected using the /mSQLCMD parameter, I can only see the primary MDF and the log file. I was unable to see the additional NDF files that I needed to reduce the file sizes on. This is described in the MS article: https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server.

    To resolve the matter, I had to restart the SQL server multiple times and run DBCC FREEPROCCACHE multiple times. It had to empty/delete each individual ndf file and sometimes restart SQL again.

    Once I got rid of some of the extra NDFs, everything return to "normal". I was then able to reduce the final NDFs manually. I did a final restart to make sure everything all the data files return to starting sizes.