October 9, 2012 at 11:14 am
I'm currently running SQL 2012 and having some issues altering the TempDB to remove two additional mdf data files from the filegroup.
The tempdb currently has two additional mdf data files, tempdev1, tempdev2
I'm running the following command
ALTER DATABASE tempdb REMOVE FILE tempdev2
And while I expect to see the message
Msg 5042, Level 16, State 1, Line 1
The file ‘tempdev2′ cannot be removed because it is not empty.
I am NOT seeing anything to the likes below...which concerns me as I don't think i'm ready to restart my instance and have the desired changes in play
The file “D:\tempdb\tempdev2.mdf” has been modified in the systemcatalog. The new path will be used the next time the database isstarted.
October 9, 2012 at 11:14 am
I also tried the following, but no luck here, got the following message...
USE tempdb;
GO
DBCC SHRINKFILE('tempdev1', EMPTYFILE)
USE master;
ALTER DATABASE tempdb
REMOVE FILE tempdev1;
GO
DBCC SHRINKFILE: Page 3:511072 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1
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.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
October 9, 2012 at 11:22 am
Last update...
It seems that in 2008, i can run the alter command and get confirmation that something was updated in the system catalog along with the error about the file not being empty
In 2012, it just gives the error that the file is not empty
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply