Remove TempDB ndf file

  • Hi,

    I have added ndf to tempdb for checking performance improvement....

    Now i want to remove the ndf file... I am using below command...

    USE tempdb

    GO

    DBCC SHRINKFILE (3, TRUNCATEONLY);

    GO

    use master

    go

    ALTER DATABASE TEMPDB Remove FILE tempdev1

    Results:

    DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages

    23766476643232

    Error:-

    (1 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Msg 5042, Level 16, State 1, Line 1

    The file 'tempdev1' cannot be removed because it is not empty.

    Note:

    =>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error...

    => I executed above commands and restarted services...no change...

    Can any one please help me to remove / drop ndf file...

    Thanks,

    SP

  • Use this command to empty the contents of the file. Only when the file is not used it can be removed.

    DBCC SHRINKFILE (N'{datafile_name}' , EMPTYFILE)

    You can also just remove the file from the properties of the database. When you restart the SQL Server service the [tempdb] is (re-)created.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • I am getting below error when i use same DBCC command

    USE tempdb

    GO

    DBCC SHRINKFILE (N'tempdev1' , EMPTYFILE)

    GO

    DBCC SHRINKFILE: Page 3:48 could not be moved because it is a work table page.

    Msg 2555, Level 16, State 1, Line 2

    Cannot move all contents of file "tempdev1" to other places to complete the emptyfile operation.

    I checked temp tables also..not existed....

    I restarted and executed below ones immdtly....

    USE tempdb;

    GO

    DBCC SHRINKFILE('tempdev1', EMPTYFILE)

    GO

    USE master;

    GO

    ALTER DATABASE tempdb

    REMOVE FILE tempdev1;

    same errors.....

  • Pulivarthi Sasidhar (7/8/2014)


    DBCC SHRINKFILE: Page 3:48 could not be moved because it is a work table page.

    Msg 2555, Level 16, State 1, Line 2

    Cannot move all contents of file "tempdev1" to other places to complete the emptyfile operation.

    This is a common error when you try to empty a file from [temdb]. Because [tempdb] is heavily used by both system- and user-processes it will allmost allways have used objects inside.

    But the error message you get when you execute the ALTER DATABASE command should also indicate the system catalog was modified. This change would be in affect when the service is restarten. So when you restart the SQL Server service the file should be removed from the database (but still exists on disk and needs to be manually removed). See also http://www.daveturpin.com/2011/07/how-to-drop-a-tempdb-database-file/

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Yes...Indeed...The Some of processes are running behind the scene.... Iexecuted below one after Server restarts then TempDB Properties=> Files=> Select tempdev1=> clicked on Remove button...(SSMS)

    use [tempdb]

    go

    DBCC FREEPROCCACHE

    Its resolved.....

  • I tried to delete one of the ndf files for the tempDB and got the following error:

    DBCC SHRINKFILE: Page 4:24 could not be moved because it is a work table page.

    Msg 2555, Level 16, State 1, Line 8

    Cannot move all contents of file "tempdev_xxx" to other places to complete the emptyfile operation.

    The issue was resolved by the following batches

    USE [tempdb]

    GO

    DBCC DROPCLEANBUFFERS

    GO

    DBCC FREEPROCCACHE

    GO

    DBCC FREESESSIONCACHE

    GO

    DBCC FREESYSTEMCACHE ( 'ALL')

    GO

    DBCC SHRINKFILE (N'tempdev_test2' , EMPTYFILE)

    GO

    ALTER DATABASE [tempdb] REMOVE FILE [tempdev_test2]

    GO

  • CozzaroNero (5/22/2015)


    I tried to delete one of the ndf files for the tempDB and got the following error:

    DBCC SHRINKFILE: Page 4:24 could not be moved because it is a work table page.

    Msg 2555, Level 16, State 1, Line 8

    Cannot move all contents of file "tempdev_xxx" to other places to complete the emptyfile operation.

    The issue was resolved by the following batches

    USE [tempdb]

    GO

    DBCC DROPCLEANBUFFERS

    GO

    DBCC FREEPROCCACHE

    GO

    DBCC FREESESSIONCACHE

    GO

    DBCC FREESYSTEMCACHE ( 'ALL')

    GO

    DBCC SHRINKFILE (N'tempdev_test2' , EMPTYFILE)

    GO

    ALTER DATABASE [tempdb] REMOVE FILE [tempdev_test2]

    GO

    You described a workaround that will work in most cases but it has a HUGE drawback!!! You have cleared all the memory of SQL server which results in a severe performance hit. SQL has to re-generate query plans and reload all the data from disk into memory, resulting in high I/O and duration of actions. Your method is absolutely not recommended to perform in a production environment!!

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • You right and I should have spent a few words on it...

    We tried to remove the ndf files already two times with no luck therefore, we have planned to go with these batches on a Thursday night just before May Bank holiday followed by 1 week half-term schools holidays, which means a very quite business period therefore, all plans can be rebuilt during the next coming days without affecting the performance of the server too much...

    On Friday, no slowness or complain from the business users and we expect the same trend for the coming week.

    Note. the first time we tried to remove the ndf files, we restarted the server in single user mode and despite of that, I had no luck

    To be honest, I'm not quite sure why it worked, I didn't expect tempDB being somehow connected to all those query plans and so on...

    Thanks to bring that up!

  • I tried all the above and I still could not remove the tempdb file.  I was trying to fix the issue on an AlwaysOn secondary node and found that I had to:

    1. suspend data movement on all DB's in the AG
    2. restart SQL
    3. run the above scripts to flush the caches
    4. Empty the file and then remove it

     

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply