Mar 20, 2017 8:30:12 PM by Pinal Dave
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?
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.
NET START MSSQLSERVER /f /mSQLCMD
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev',SIZE=500MB, FILEGROWTH = 500MB )
SELECT name ,size*8.0/1024 'Initial Size in MB'
FROM master.sys.sysaltfiles WHERE dbid = 2
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)
Tags: SQL Server
Written by 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.