Tempdb move issue

tempdb

In preparation for moving many of my databases in a data warehouse to READ COMMITTED SNAPSHOT isolation level (CDC and Reads contend for locks) I added a drive and moved my tempdb there (mdf and ldf) using the ALTER DATABASE command, or at least I thought I did. My script didn't include my 7 ndf files so I moved them with the same command. Now, when I look at the tempdb properties or with sp_help, none of the ndf files are listed and they aren't in the Primary filegroup (I only have the one file group). Can I re-associate the ndf files with the filegroup and if so how?

BTW: MS SQL2016 SP1, OS WinServ 2012 R2 (x64)

Best Answer

Cliff.

The easiest way is to do the following:

  1. Add as many files as you need to TempDB (but take a look at MS recommendations first; also reading this Brent Ozar post might be helpful) with this T-SQL:

    ALTER DATABASE tempdb ADD FILE(NAME = N'<LogicalName>', FILENAME = N'<path&filename>', SIZE = <size>, FILEGROWTH = <growth>); GO

  2. Restart SQL Server service

These two simple actions should help you solve your issue!

Related Question