.ndf already in use by tempdb?

Question

.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

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

When you say stop and start tempdb. Do you mean stop and start sqlserver?

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

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

May 18, 2012 at 7:29 am

#1489679

yes - sorry - a typo on my part

MVDBA

DBA_Dom SSCrazy Points: 2902 More actions · Answer 6

Yes, it will need a different file name.

logical file name tempdb_data1, physical file name T:\tempdb_data1.ndf

logical file name tempdb_data2, physical file name T:\tempdb_data2.ndf

etc...