TempDB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

TempDB

Author

Topic

dboiler1
Starting Member

39 Posts

Posted - 2008-01-14 : 15:53:14

For a variety of reasons, the C drive on our Production Server is low on disk space. When we ordered the server it came installed with SQL Server 2005 and they defaulted the System databases to the C drive. All our client dbs are on the F drive. The tempdb database gets fairly large every day and consumes a large portion of what is left of the C drive. Every evening, after work hours, I have to stop SQL Server, delete the tempdb.mdf and ldf files and restart. Is there a way to move the default location of the tempdb files to the F drive? I do not want to repartition the hard drive or uninstall and reinstall SQL Server.

Thanks in advance,

Dave

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-14 : 15:57:14

Change locations with following code, and then restart SQL Server.

By the way, this is documented in SQL Server Books Online.


use master
go
ALTER DATABASE tempdb
MODIFY FILE
	(
	NAME = tempdev ,
	FILENAME = 'E:\MSSQL\data\tempdb.mdf'
	)
GO
ALTER DATABASE tempdb
MODIFY FILE
	(
	NAME = templog ,
	FILENAME = 'H:\MSSQL\data\templog.ldf'
	)

CODO ERGO SUM

dboiler1
Starting Member

39 Posts

Posted - 2008-01-14 : 16:27:43

Thanks Michael, I will give it a go tonight.

Dave

dboiler1
Starting Member

39 Posts

Posted - 2008-01-14 : 22:10:35

quote:
Originally posted by Michael Valentine Jones

Change locations with following code, and then restart SQL Server.

By the way, this is documented in SQL Server Books Online.

use master
go
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev ,
FILENAME = 'E:\MSSQL\data\tempdb.mdf'
)
GO
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = templog ,
FILENAME = 'H:\MSSQL\data\templog.ldf'
)

CODO ERGO SUM

Worked great Michael!! Thanks much.

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources