The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob

  Wednesday, September 19, 2018

 


Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

 

Family Pool August 2007_02.JPG

  bob Links
SQL Server, SMS, Exc...
Keep your libraries, keep your penal institutions, keep your insane asylums…give me beer. You think man needs rule, he needs beer. The world does not need morals, it needs beer. It does not need your lectures and charity. The souls of men have been fed with indigestibles, but the sould could make use of beer. - Henry Miller, "make beer for man"
Wider View Insert

Cluster
DOS
IBM - AS400
MOM
Performance Counters
Service Broker
SQL Server
     Temporary Database
          TempDB Monitoring an...
          Moving the tempdb da...
          TempDB Multiple File...
          Moving the Temporary...
     Views
     Backup
     SQL Mail
     DTS - Data Transformation Services
     Memory
     Security
     Excel
     Jobs SQL Agent
     MSDE
     Install
     DBCC
     User Management
     System Databases
     BCV
     Deadlocks
     Temp Tables
Windows OS

Insert Category

Will be added as a sub-category of, Temporary Database
Moving the tempdb database

Moving the tempdb database

You can move tempdb files by using the ALTER DATABASE statement.

 

1.

Determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb go sp_helpfile go

 

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

 

2.

Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master go Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf') go Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf') go

 

You should receive the following messages that confirm the change:

 

Message 1

 

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

 

Message 2

 

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

 

3.

Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

 

4.

Stop and then restart SQL Server.