.ndf already in use by tempdb?

Question

Post reply

.ndf already in use by tempdb?

acorrei1

SSC Veteran

Points: 223
More actions
May 18, 2012 at 6:43 am

#256653

I'm trying to add a secondary data file to temp to help with performance but it's coming with an error:

The file 'X:\xxxx\xxxxx.ndf' cannot be overwritten. It is being used by database 'tempdb'.

Is there some cleanup that needs to be done somewhere or do I HAVE to create the .ndf under a different name?

Viewing 7 posts - 1 through 6 (of 6 total)

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Can you post the command(s) you are sending to accomplish this, please? First thoughts are that you are specifying the existing .ndf and if the DB is attached to the instance, you won't be able to overwrite this. For a new ndf ensure you are specifying a different filename.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

acorrei1 SSC Veteran Points: 223 More actions · Answer 2

acorrei1

SSC Veteran

Points: 223

May 18, 2012 at 7:13 am

#1489658

Trying to do a DR test. The file did exist but got deleted. I'm trying to recreate it.

GO

ALTER DATABASE tempdb

ADD FILE

(

NAME = tempdb_data_04,

FILENAME = 'x:\xxx\xxxx.ndf',

SIZE = 2MB,

FILEGROWTH = 1MB

);

GO

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

acorrei1 (5/18/2012)

Trying to do a DR test. The file did exist but got deleted. I'm trying to recreate it.

GO

ALTER DATABASE tempdb

ADD FILE

(

NAME = tempdb_data_04,

FILENAME = 'x:\xxx\xxxx.ndf',

SIZE = 2MB,

FILEGROWTH = 1MB

);

GO

but if you stop and restart tempdb it automatically re-creates the deleted files - as soon as you start sql it will re-create , thefore you're just trying to re-add a file that has already been added

MVDBA

acorrei1 SSC Veteran Points: 223 More actions · Answer 4

michael vessey (5/18/2012)

acorrei1 (5/18/2012)

Trying to do a DR test. The file did exist but got deleted. I'm trying to recreate it.

GO

ALTER DATABASE tempdb

ADD FILE

(

NAME = tempdb_data_04,

FILENAME = 'x:\xxx\xxxx.ndf',

SIZE = 2MB,

FILEGROWTH = 1MB

);

GO

but if you stop and restart tempdb it automatically re-creates the deleted files - as soon as you start sql it will re-create , thefore you're just trying to re-add a file that has already been added