Skip to content

NKCODE TECH GEEK ZONE

  • RSS - Posts
Menu
  • Home
  • Cloud
    • Azure
    • Alibaba
    • AWS
  • Hardware
  • Linux
  • Network
  • Security
  • Windows Client / Servers
    • SQL
    • Windows Client OS
      • Windows 10
    • Windows Servers
      • Windows 2008R2
      • Windows Server 2012R2
      • Windows Server 2016
      • Windows Server 2019
  • VMWARE
  • Free Tools
  • About Me
    • Disclaimer
Menu

TempDB Move from one drive to another drive

Posted on January 27, 2018

If you ever find your TEmpDB to be full and if you want to move TempDB, you will find this blog post very helpful.

Here is the error message which may come across. 

Event ID: 17052
Description: The LOG FILE FOR DATABASE ‘tempdb‘ IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
Up SOME LOG SPACE

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to another drive.
1) TempDB grows bigger and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on a different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move the database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.

1
2
3
4
USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:\Program Files\Microsoft SQL ServerMSSQL\datatempdb.mdf PRIMARY 16000 KB
templog 2 C:\Program Files\Microsoft SQL ServerMSSQL\datatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usuallytempdev and templog by default. These names will be used in the next statement. Run following code, to move mdf and ldf files.

1
2
3
4
5
6
7
8
USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\datatemplog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Share this:

  • Click to share on X (Opens in new window) X
  • Click to share on Facebook (Opens in new window) Facebook
  • Click to share on LinkedIn (Opens in new window) LinkedIn
  • Click to share on Pinterest (Opens in new window) Pinterest
  • Click to share on Telegram (Opens in new window) Telegram
  • Click to share on WhatsApp (Opens in new window) WhatsApp
  • Click to share on Reddit (Opens in new window) Reddit
  • Click to email a link to a friend (Opens in new window) Email

Like this:

Like Loading...

Related

Welcome to Teck Geek Zone

Alibaba & Azure Cloud with a free trial worth $200-1200 USD Click below Cloud Providers

  • 🆚 Modern Identity Wars: Active Directory vs. Microsoft Entra ID
  • 🔷 VMware Cloud Foundation 9 (VCF 9.0): Redefining the Modern Private Cloud
  • From ARM to HCL: Exploring Azure’s New Export Template to Terraform Feature
  • 🛡️ Strengthening Your AWS Security: Best Practices Explored
  • From Backup Center to Business Continuity Center: A New Era in Azure BCDR Management

Categories

  • Cloud (178)
    • Alibaba (38)
    • AWS (36)
    • Azure (109)
  • Free Tools (5)
  • Hardware (17)
  • Linux (13)
  • Network (28)
  • Security (21)
  • VMWARE (51)
  • Windows OS (44)
    • Windows 10 (7)
  • Windows Servers (69)
    • SQL (3)
    • Windows 2008R2 (7)
    • Windows Server 2012R2 (15)
    • Windows Server 2016 (20)
    • Windows Server 2019 (10)

Subscribe to our newsletter

©2025 NKCODE TECH GEEK ZONE | Design: Newspaperly WordPress Theme
%d