question

AnilMenariya avatar image
AnilMenariya asked

REMOVING TEMPDB DATA Files

I am trying to remove extra tempdb data files in TEST instance. Steps that I am taking.

1. Restart SQL Server (It's test, doesn't matter if I restart it)

2. There are no users on the instance, then I use the below commands.

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev1' , EMPTYFILE) GO

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev2' , EMPTYFILE) GO

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE) GO

I get below error. DBCC SHRINKFILE: Page 3:24 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 7 Cannot move all contents of file "tempdev1" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHRINKFILE: Page 4:31 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 13 Cannot move all contents of file "tempdev2" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHRINKFILE: Page 5:20 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 19 Cannot move all contents of file "tempdev3" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 3.

I tried multiple times, then I ran following command which helped in emptying the files.

DBCC FREESYSTEMCACHE ('ALL')

DBCC FREEPROCCACHE

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev1' , EMPTYFILE) GO

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev2' , EMPTYFILE) GO

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE) GO

When you restart SQL Server doesn't it do the same thing that DBCC FREESYSTEMCACHE ('ALL') and DBCC FREEPROCCACHE does?

tempdbfiles
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.