其他HD上的SQL Server 2008 TempDB

I would like to make all stuff related to TempDB be stored on a separate HD.

我想将与TempDB相关的所有内容存储在一个单独的HD上。

I have this new HD with a 500 Gb size as my E:\ drive.

我有一个500 Gb大小的新HD作为我的E:\驱动器。

How would I use or move TempDB from one drive to another drive?

我如何使用或将TempDB从一个驱动器移动到另一个驱动器?

------------------------------EDIT---------------------------
After following the tutorial, when restarting the Server I get the message:

- - - - - - - - - - - - - - - 编辑 - - - - - - - - - - --------遵循本教程后,重新启动服务器时,我收到消息:

The request failed or the service did not respond in a timely fashion. Consult the event log or other application error logs for details.

请求失败或服务未及时响应。有关详细信息,请参阅事件日志或其他应用程序错

• I can not start it anymore, any suggestion? Does it have to do with the database path. (the location of the database such as tempdb.mdf is different than the folder 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA’

我不能再开始了,有什么建议吗?是否与数据库路径有关。 (数据库的位置,如tempdb.mdf不同于文件夹'C:\ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA'

1 个解决方案

#1

This can be done in the server properties.

这可以在服务器属性中完成。

1. Right click on your server instance

右键单击您的服务器实例

2. Click "Properties"
3. Click "Database Settings"

单击“数据库设置”

4. Change "Log" to whatever path you want (including alternate HDD)

将“Log”更改为您想要的任何路径(包括备用HDD)

EDIT

I misunderstood the above question... I suppose I should learn to read. The above instructions show how to move the LOG DB to a different hard drive.

我误解了上面的问题...我想我应该学会阅读。以上说明显示如何将LOG DB移动到其他硬盘驱动器。

The instructions found HERE will show you how to move the TempDB

在此处找到的说明将向您展示如何移动TempDB

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

打开查询分析器并连接到您的服务器。运行此脚本以获取用于TempDB的文件的名称。

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:

结果将是这样的:

| name     | fileid  | filename                                                | filegroup  | size     |
|----------|---------|---------------------------------------------------------|------------|----------|
| tempdev  | 1       | C:Program FilesMicrosoft SQLServerMSSQLdatatempdb.mdf   | PRIMARY    | 16000 KB |
| templog  | 2       | C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf | NULL       | 1024 KB  |

Along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.

以及与数据库相关的其他信息。默认情况下,文件的名称通常是tempdev和demplog。这些名称将在下一个声明中使用。运行以下代码,以移动mdf和ldf文件。

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.

TempDB的定义已更改。但是,在SQL Server重新启动之前,不会对TempDB进行任何更改。请停止并重新启动SQL Server,它将在新位置创建TempDB文件。