question

vivekchandra09 avatar image
vivekchandra09 asked

SQL Server 2014, need to add two more drive and put tempdb and logfile them

We have a box where we only have two drives c and E. E has all the data, backup and logfile. How to split it up in two more drives (e.g. F and G)
sql server 2014sql-server-2014-standardbox
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
For TempDB, you just need to do ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev , FILENAME='New path to datafile') --repeat above step for each tempdb datafile if you have added more than one file to tempdb (which you should --have done if you have more than one CPU Core) ALTER DATABASE tempdb MODIFY FILE (NAME=templog , FILENAME='New path') and then you restart the SQL Server service. To verify that the files have moved after the restart: SELECT * FROM sys.master_files where database_id = DB_ID('tempdb'); When that's verified, you should physically remove the old tempdb files. For other databases, you would do the same, except when you have stopped the service, you must physically move the files yourself and then start the service. When moving MSDB, you would also want to verify that database mail still works after the relocation of the files. Finally, the master database: - In SQL Server configuration manager you right-click on the SQL Server service and in the properties dialog you change the -d and -l startup parameters (-d is master data file location, -l is master transaction log location) - Stop the service. - Physically move the files. - Restart the service - Verify with sys.master_files that everything looks right.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.