I have all system databases in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf. How do I move the system databases to G:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf? Will it cause any issues. I have one database that is existing in the current sqlserver where the datafile is in G:\Data\x.mdf

@Shashank Singh , used the below command to move the files. Deleted all tempdb datafles,log files and ndf files. USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\ tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\ templog.ldf'); GO All temp files were created: SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID('tempdb'); GO name CurrentLocation state_desc tempdev F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE templog E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE temp2 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE temp3 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE temp4 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE .ndf files did not exist prior to moving the system databases so my question was any reason why it was generated? Will the .ndf files grow?

@tibor_karaszi@hotmail.com After executing query for both current and template state got the same output: db_name_ file_id name physical_name size_MB type_desc growth max_size_MB tempdb 1 tempdev F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf 8 ROWS 64 MB 0 tempdb 3 temp2 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf 8 ROWS 64 MB 0 tempdb 4 temp3 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf 8 ROWS 64 MB 0 tempdb 5 temp4 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf 8 ROWS 64 MB 0 tempdb 2 templog E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf 8 LOG 64 MB 0 Do I need to increase the size of tempdb? Will the current configuration cause any issues?

How to move C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf to G:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf

Answer accepted by question author

Avyayah 1,291 Reputation points

2020-10-26T13:44:41.6+00:00

@Shashank Singh , used the below command to move the files. Deleted all tempdb datafles,log files and ndf files.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\ tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\ templog.ldf');
GO

All temp files were created:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

name CurrentLocation state_desc
tempdev F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ONLINE
templog E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf ONLINE
temp2 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf ONLINE
temp3 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf ONLINE
temp4 E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf ONLINE

.ndf files did not exist prior to moving the system databases so my question was any reason why it was generated? Will the .ndf files grow?
Please sign in to rate this answer.
Shashank Singh 6,251 Reputation points

2020-10-26T15:55:01.717+00:00

.ndf files did not exist prior to moving the system databases so my question was any reason why it was generated? Will the .ndf files grow?

The answer as I have already said is because you did not specifically changed the location. I just tried your scenario and could easily reproduce it. I first just changed the location of Tempdb mdf and ldf file and not the ndf files. The mdf and ldf moved to new location while ndf did not. I had to run alter command to change the location and then they moved. please note you never have to move files physically. I believe you did some mistake from your side. Yes ndf files will grow unless you stop autogrowth on it

Avyayah 1,291 Reputation points

2020-10-27T22:39:02.543+00:00

I executed the following statement

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\ tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\Log\MSSQL13.MSSQLSERVER\MSSQL\DATA\ templog.ldf');
GO

Stopped Sqlserver Service, deleted temp datafile, logfile and ndf file. But since the information is in the master database temp datafile and logfile are created as well as ndf files. I don't think I can delete .ndf files.

Shashank Singh 6,251 Reputation points

2020-10-28T03:14:57.49+00:00

The above statement ONLY moves temodb mdf and ldf files, it does not moves ndf file. So if you delete all the files, post startup, tempdb mdf and ndf files will be created on new location while ndf will be recreated on old location. When tempdb is stopped you can delete all the files associated with it. For tempdb to work you only need mdf and ldf files. Ndf is added for tempdb performance

Avyayah 1,291 Reputation points

2020-10-28T04:30:08.87+00:00

Got it, it looks like it is normal to for tempdb to ndf files. Any advise on the size of ndf files that I should check on the server?

Shashank Singh 6,251 Reputation points

2020-10-28T04:43:04.12+00:00

The so called "initial size" of all the tempdb files (mdf +ndf) should be same and autogrowth(AG) should be same. Do not keep AG in percentage. You do not need to enable TF 1117, 1118 as your have SQL Server 2016. Also note since you have SQL Server 2016 you do not also have to keep AG value same as system takes care of that but doing beforehand is not going to harm. Just look at your tempdb growth and size the data files accordingly. If the thread has answered your question please close it by marking answer, and voting helpful on answer which has helped you.

tibor_karaszi@hotmail.com 4,316 Reputation points

2020-10-28T09:41:36.137+00:00

I replied to you yesterday, but you didn't pick up on that.

You need to move all the files that you want to move.

If you have more files than you want, you can remove them,

In general we have one data file per processor core, up to 8 files.

As for how large file files should be, we can't say that. Run SQL for a while and see what size they grow to. That is the size for tempdb that you need, apparently.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to move C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf to G:\Data\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf

9 additional answers

Your answer