Can I move the tempdb database to a separate drive and leave the rest of the system databases (master, msdb) on a separate drive? Please advice, I would like to apply this to the production database so that the system does not shutdown.
Yes, use the alter database command. Of course change the drive letter and location in the script below to your naming standard and drive(s). USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'e:\tempdb\datatempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'f:\tempdb\datatemplog.ldf') GO Then restart your sql service.
Just to add...tempdb is recreated each time the SQL Service starts, hence why you have to restart after changing the locations, so be aware that the 'old' tempdb files will still exist in the original location once you have restarted. These can be safely deleted once the 'new' tempdb is being used.
Also, just to add, size the tempdb data files properly so they do not autogrow in tiny chunks every time causing unnecessary fragmentation. Same goes for the log file. Take into consideration that the log file can take some time to create at service startup if its large, as the database will zero-out the file.