July 8, 2014 at 3:36 am
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
July 8, 2014 at 4:00 am
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.
July 8, 2014 at 4:21 am
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.....
July 8, 2014 at 4:41 am
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/
July 8, 2014 at 6:27 am
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.....
May 22, 2015 at 9:15 am
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
May 22, 2015 at 2:33 pm
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!!
May 22, 2015 at 4:32 pm
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!
June 26, 2020 at 10:36 am
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:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply