Failed "ALTER DATABASE...REMOVE FILE" leaves sys.database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Failed "ALTER DATABASE...REMOVE FILE" leaves...

Failed "ALTER DATABASE...REMOVE FILE" leaves sys.database_files missing a file

Rate Topic

Display Mode

Topic Options

Author

Message

CaptainSlock

Posted Thursday, December 15, 2011 5:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 22, 2013 4:49 PM
Points: 1, Visits: 106

From SSMS I removed a secondary file (named tempdev2) in the PRIMARY filegroup of tempdb and lowered the maximum size for tempdev. It erred out saying:

The file "K:\sqldata\PROD_CRICKET\tempdev2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.

I cancelled out of the tempdb properties and then opened them back up and noticed that my max size change was reflected and strangely, tempdev2 had disappeared. A little suspicious, I checked the file system and discovered that tempdev2 still existed. Deleting it from the OS fails, as it's still in use. I then ran DBCC SHRINKFILE(tempdev2,EMPTYFILE), but received:

Could not locate file 'tempdev2' for database 'tempdb' in sys.database_files. The file either does not exist, or was dropped.

When I look in sys.database_files, I see tempdev2 (fileid 3), but when I'm in sys.master_files I do not. Any idea what happened and more importantly, how I can resolve this without restarting the instance?

Thanks for your help.

Post #1222816

codebyo

Posted Wednesday, January 11, 2012 2:27 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436

I just had the same problem here tonight.
I was making a little test creating extra files for tempDB and ended up with this weird behavior.

Has anyone experienced this? Is this a confirmed bug?

I'm afraid of using ALTER DATABASE REMOVE FILE until I understand what's really going on. Unsure

[EDIT] For those who are having this problem you must restart your SQL Server engine process for things to show correctly. Stupid me!

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2

Post #1234388

« Prev Topic | Next Topic »

Permissions