How to move TempDB files from one drive to another

Recently I had a request to move TempDB files from one drive to another because of space issues. I would want to share the knowledge throgh this article.

As far as Production environment is concern, this task should be taken care by DBA. Albeit, you can do it in Development environment. Below are the steps to transfer TempDB files:

1.  Get Current TempDB Files Location
Use following code to get the current TempDB files location:  

USE TempDB
GO
sp_HelpFile
GO




You might need these files if something goes wrong.


2. Verify New File Location
Before moving files to new location, verify that SQL Server has access to the new location. I will use "T:\MSSQL\Data" as new file location.


3. Use T-SQL command to specify new location:

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


4. Restart SQL Server Services
SQL Serve will not use new file location unless you restart SQL Server Services. Once you restart the SQL Services, you can delete the old files (mentioned in Step 1).

 

How to find all the IDENTITY columns in a database?

Here is the easiest way to list all the IDENTITY Columns of any database:

SELECTOBJECT_NAME([object_id]) as TableName,

name as ColumnName

FROM [DatabaseName].sys.columns

WHERE is_identity = 1

Function to Convert Decimal Number into Binary, Ternary, and Octal

In this article I am sharing user defined function to convert a decimal number into Binary, Ternary, and Octalequivalent.

IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO


CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
  decNum [int] PRIMARY KEY NOT NULL,
  NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
  WITH tblBase AS
  (
    SELECT CAST(0 AS VARCHAR(50)) AS baseNum
    UNION ALL
    SELECT CAST((baseNum + 1) AS VARCHAR(50))
    FROM tblBase WHERE baseNum < @base-1
  ),
  numbers AS
  (
    SELECT CAST(baseNum AS VARCHAR(50)) AS num
    FROM tblBase
    UNION ALL
    SELECT CAST((t2.baseNum + num) AS VARCHAR(50))
    FROM numbers CROSS JOIN tblBase t2
    WHERE LEN(NUM) < @lenght
  )

  INSERT INTO @NumbersBaseN
  SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
  FROM numbers WHERE LEN(NUM) > @lenght - 1

  OPTION (MAXRECURSION 0);
  RETURN
END
GO


-- Unit Test --
-- Example with decimal, binary, ternary and octal


SELECT
   U1.decNum   AS Base10,
   U1.NumBaseN AS Base2,
   U2.NumBaseN AS Base3,
   U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
  ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
  ON u2.decNum = u3.decNum


Here is the output: