Move Tempdb Issues

[Steffi1013]

I have moved tempdb by running the following statement:
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev8, FILENAME = 'T:\MSSQL\DATA\tempdev8.mdf');
GO

Then I restarted SQL Server and ran the following:
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

Which shows that that tempdev8 is located at:
T:\MSSQL\DATA\tempdev8.mdf

BUT... when I go into Management Studio and look at the properties on tempdb, you do NOT see that file listed there! Also, when you run sp_helpdb 'tempdb', it does NOT show that file either.

What must I do for Managment Studio and sp_helpdb to show all the files??

Thanks!

 

[JayKusch]

By default, unless you changed something on TempDB in the past, the default file name for the MDF is TEMPDEV and the LDF is TEMPLOG.

Then when you add additional files to TempDB for performance reasons, NDFs are created for the addition files. Each new TempDevX.NDF has a sequential number.

So you first secondary data file would be TempDev2, the next TempDev3 and so on.

NOW you can assign the logical name to be something other than the default TempDevX but I would not know why you would do this.

So in say all that, when I see TempDev8.MDF, is that really the name of the primary data file for TempDB (the MDF) or is that an error?



ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev8, FILENAME = 'T:\MSSQL\DATA\tempdev8.mdf');
GO


Thanks

J. Kusch

 

[Steffi1013]

These are the 3 files... there are actually 2 mdf's!

ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev8, FILENAME = 'T:\MSSQL\DATA\tempdev8.mdf');
GO

So, in order to fix this problem -- do I need to delete the tempdev8.mdf from the t:\mssql\data directory and recreate the file so that it has an .ndf extension??

If I delete the file from the directory path above, will it stop showing up in sys.master_files?

Also, will I need to restart SQL Server after doing all this?

 

[JayKusch]

Correct ... delete the TempDev8 file (which "should" have the NDF extension) within Mgt Studio. This will delete it off the drive.

Then for good measure, restart the SQL Server service.

Then you can add the file back for performance gains if you are using SQL 2005.

You will create a secondary data file (NDF) per processor\core.

If you need a script to read your registry for the number of procs\cores and create the necessary data files, let me know and I will pass that along.

Thanks

J. Kusch

 

[Steffi1013]

When you say to delete within Management Studio -- am I actually deleting from sys.master_files?

And, yes, I would love any scripts you could share with me!

 

[JayKusch]

OK ... making an assumption we are talking SQL 05 or 08 since you mention Management Studio (MS) so here it goes ...

In MS drill down in the Databases..System Databases..TempDB and right-click choosing Properties.

Choose the Files section and in there you will see the 3 files that comprise TempDB.

Click on the TemDev8 file and then hit the Remove button.

DONE ...

Here is the script I mentioned. It will create and stored procedure that you can then pass the size of each file that will be created, how large the new extents will be when the DB needs to grow and then the drive that the files need to live on.

The following code show how to execute the stored proc that will then create a new secondary datafile per proc/core on the system that will be 1GB in size, will have future extenstion of 200MB and will be place on the X: drive.

-- EXEC usp_AddTempDBFiles 1000, 200, 'X'

You will also need to tweak the stored proc before exeuting. I use a folder called TempDB_Data and TempDB_Log that live on my X: drive. That way they are easily identified and I can move the folders to other drives if need be.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[usp_AddTempDBFiles]
	(	@Size	Int, 
		@Growth	Int, 
		@Drive	Char(1)	)
  AS

/*==============================================================================
  Purpose:  To add 1 TempDB data file (NDF) per proc/core on the server for 
			better performance. 
 
  Inputs:   @Size is used for the initial size the NDF should be created
			on startup of the SQL Server service and/or server
 
			@Growth is the size the NDF is permitted to extend when it needs more
			room
 
			@Drive is the drive letter where the TempDB_Data and TempDB_Log folders
			are located or needed to be created on.

			Example:	EXEC usp_AddTempDBFiles 1000, 200, 'X'

				This will created the NDFs at 1GB in size, able to create 200MB extents when
				the file(s) need to grow and are located on the X: in the TempDB_Data and
				TempDB_Log folders respectively.

  NOTES:    
*/
--
SET NOCOUNT ON

DECLARE	@RegOutput						VarChar(20), 
		@NbrProcs						Int, 
		@SQL_Command					nVarChar(4000), 
		@TempDBDataFileCount			TinyInt, 
		@TempDBDataFileSize				Int,
		@TempDBFirstLogicalName			SysName, 
		@TempDBDataFileId				TinyInt, 
		@StartID						TinyInt, 
		@EndID							TinyInt,
		@TempDBMissingDataFileCount		TinyInt, 
		@TempDBLogicalName				SysName, 
		@TempDBFirstPhysicalPath		nVarChar(260)
--
--  Create secondary TempDB Files based on the number of procs
--		in the server
--

EXEC	Master..xp_regread 
		@rootkey	= 'HKEY_LOCAL_MACHINE', 
		@key		= 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
		@value_name	= 'NUMBER_OF_PROCESSORS',
		@value		= @RegOutput OUTPUT

SET	@NbrProcs = @RegOutput

SELECT	@StartID	= 2, 
		@EndID		= 2, 
		@TempDBDataFileCount = COUNT(*), 
		@TempDBDataFileId = MIN(File_ID) 
FROM	TempDB.Sys.Database_Files 
WHERE	Type = 0

SET @TempDBMissingDataFileCount = @NbrProcs - @TempDBDataFileCount

IF @TempDBDataFileCount <> @NbrProcs
  BEGIN
	SELECT	@TempDBFirstLogicalName = [Name], 
			@TempDBFirstPhysicalPath = 
            SUBSTRING(physical_name, 
			1, 
			LEN(Physical_Name) - CHARINDEX('\', REVERSE(Physical_Name )) + 1)
	FROM	TempDB.Sys.Database_Files 
	WHERE	File_ID	= @TempDBDataFileId

    SET @TempDBDataFileId = 0

    -- file exists, expand it, if necessary. to make sure they will all be the same
    WHILE	@TempDBDataFileCount <> 0
	  BEGIN

        SELECT	@TempDBDataFileId = MIN(File_ID)
        FROM	TempDB.Sys.Database_Files
        WHERE	Type = 0 
		AND		File_ID > @TempDBDataFileId

		SELECT	@TempDBLogicalName	= [Name], 
				@TempDBDataFileSize	= [Size]
		FROM	TempDB.Sys.Database_Files 
		WHERE	File_ID = @TempDBDataFileId

		IF	@size*1024/8 > @TempDBDataFileSize
		  BEGIN
			SET	@SQL_Command =	'ALTER DATABASE TempDB MODIFY FILE (NAME = N'''	+ 
								@TempDBLogicalName								+ 
								''', SIZE = ' 

			SET @SQL_Command =	@SQL_Command					+ 
								CONVERT(VarChar(10), @size)		+ 
								'MB, FILEGROWTH = '				+ 
								CONVERT(VarChar(10), @growth)	+ 
								'MB)'

			--PRINT @SQL_Command

			EXEC (@SQL_Command)
		  END

		SET @TempDBDataFileCount = @TempDBDataFileCount - 1
    END

    -- Adding new data files (NDFs)

	WHILE @TempDBMissingDataFileCount <> 0
	  BEGIN
		WHILE 1 = 1
		  BEGIN
			IF EXISTS (
				SELECT	* 
				FROM	TempDB.sys.database_files 
				WHERE	[Name] = @TempDBFirstLogicalName + CONVERT(VarChar(3), @StartID) )

				SET @StartID = @StartID + 1
			ELSE
				BREAK
		  END
		WHILE 1 = 1
		  BEGIN
			IF EXISTS (
				SELECT	* 
				FROM	TempDB.Sys.Database_Files 
				WHERE	physical_name = @TempDBFirstPhysicalPath + @TempDBFirstLogicalName + CONVERT(VarChar(3), @EndID) + '.ndf' )

				SET @EndID = @EndID + 1
			ELSE
				BREAK
		  END

		SET @SQL_Command =	'ALTER DATABASE TempDB ADD FILE (NAME = N'''	+ 
							@TempDBFirstLogicalName							+ 
							CONVERT(VarChar(3), @StartID)

		SET @SQL_Command =	@SQL_Command				+ 
							''', FILENAME = N'''		+ 
							@TempDBFirstPhysicalPath	+ 
							@TempDBFirstLogicalName		+ 
							CONVERT(VarChar(3), @EndID)

		SET @SQL_Command =	@SQL_Command				+ 
							'.ndf'						+ 
							''', SIZE = '				+ 
							CONVERT(VarChar(10), @size) + 
							'MB, FILEGROWTH = '

		SET @SQL_Command =	@SQL_Command					+ 
							CONVERT(VarChar(10), @growth)	+ 
							'MB)'

		--PRINT @SQL_Command

		EXEC (@SQL_Command)

		SET @TempDBMissingDataFileCount = @TempDBMissingDataFileCount - 1
	  END
END
--
--  Set TempDB Files in their correct folders
--

-- Create the TempDB_Data folder on the target drive if needed

SET		@SQL_Command = 'MD ' + @Drive + ':\TempDB_Data'
EXEC	Master..xp_CmdShell @SQL_Command

-- Create the TempDB_Log folder on the target drive if needed

SET		@SQL_Command = 'MD ' + @Drive + ':\TempDB_Log'
EXEC	Master..xp_CmdShell @SQL_Command

-- Move the TempDB MDF file to the folder on the target drive

SET	@SQL_Command	= 'ALTER DATABASE TempDB MODIFY FILE (Name = TempDev,  Filename = ' + CHAR(39) + @Drive + ':\TempDB_Data\TempDev.MDF' + CHAR(39) + ')'

EXEC (@SQL_Command)

-- Move the TempDB LDF file to the folder on the target drive

SET	@SQL_Command	= 'ALTER DATABASE TempDB MODIFY FILE (Name = TempLog,  Filename = ' + CHAR(39) + @Drive + ':\TempDB_Log\TempLog.LDF' + CHAR(39) + ')'

EXEC (@SQL_Command)

-- Move the TempDB NDF(s) file to the folder on the target drive

SET	@StartID = 2

WHILE	@StartID <= @NbrProcs

  BEGIN

	SET	@SQL_Command = 'ALTER DATABASE TempDB MODIFY FILE (Name = TempDev' + CONVERT(VarChar,@StartID) + ',  Filename = ' + CHAR(39) + @Drive + ':\TempDB_Data\TempDev' + CONVERT(VarChar,@StartID) + '.NDF' + CHAR(39) + ')'

	EXEC (@SQL_Command)

	SET	@StartID = @StartID + 1

  END

Thanks

J. Kusch

 

[Steffi1013]

Ok - the thing is that the file tempdev8 does NOT show up in the files list in Management Studio (yes, I am using 2005). The only place I see the file is if I run the following statment :

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

And if I go to the actual directory on the server, I can also see the file tempdev8.mdf.

Can I just delete the file from the directory and also should I go into sys.master_files and delete it there also?

Thanks!

 

[JayKusch]

If it is truely part of the TempDB DB, you will not be able to delete it from the folder because it will be in use.

Before deleting it, try renaming the extension to .NDF instead of .MDF and lets see if you are able to do that.

Thanks

J. Kusch

 

[Steffi1013]

Yes, it did allow me to rename to .ndf and it shows it now as a Microsoft secondary file...

What next?

 

[JayKusch]

WELL ... if you were able to change it ... it is not in use.

Just want to make sure of one thing first ... the SQL Server service WAS RUNNING when you did the rename ... CORRECT?

Thanks

J. Kusch

 

[Steffi1013]

Yes, it was running and still is... so if I can rename them, then I can safely assume they are NOT in use and delete them, correct?

 

[JayKusch]

just to be SAFE ... I would copy them to a holding folder, preferably on another disc (say C.

Another question ... you said: "so if I can rename them, then I can safely assume they are NOT in use and delete them" ...

The word THEM interests me. Are you able to rename the original MDF and LDF too or are you only able to rename the TempDev8.xxx file?

Thanks

J. Kusch

 

[Steffi1013]

Just the TempDev8.mdf -- excuse my use of "them"! I am going nuts trying to fix this issue and am typing quicker than my brain is working.

I will try what you recommend and let you know what happens.

 

[Steffi1013]

I was able to delete the files and then through the GUI add new files and they were added as .NDF - now my only question is that when I run the following :

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

The old files still show up even though they have been deleted. How do I get them NOT to show up in sys.master_files??

 

[JayKusch]

Well, I never like to muck with system tables, though I have - lol, so in saying that we should be cautious.

I would first write down the data/columns in the table for the rouge file in case we need to recreate the record.

Then I would create a backup of the Master DB and save it away somewhere.

Now we are once again using the words "files, them and they"

The old files still show up even though they have been deleted. How do I get them NOT to show up in sys.master_files??

but I am hoping we are still just refering to the single file TempDev8 ... Correct?

If so, then I would just rename the file to something like TempDev_DELME and restart your server. See if the TempDB comes online as expected.

I would then run with this for a few days, or as long as you can.

If all seems fine, you could be relatively sure that deleting the record from the table will cause no ill effects.

Once the record has been deleted, once again restart the services to make sure TempDB fires up fine.

Thanks

J. Kusch