SQLServer 数据库 TEMPDB 数据库文件迁移

背景

TEMP数据库过大，存在于C盘，单机环境下操作比较简单，仅需要重启即可重建tempdb数据库；AlwaysOn群集环境同样，重启服务即可重建本机节点tempdb数据库；但以上操作治标不治本，为彻底解决temp数据库过大问题（实质是tempdb数据库默认存放在C盘，而一般服务器C盘空间有限），我们需要将tempdb数据库转移位置放在其他盘符。

过程

1、找到原tempdb对应文件逻辑名及实际文件存放位置

select name,physical_name from sys.master_files where database_id=db_id('tempdb')

新存放tempdb数据路径如下

F:\db

2、数据库中修改对应存放位置

示例执行语句：

ALTER DATABASE tempdb    MODIFY FILE (NAME = tempdev, FILENAME = 'D:\DataBaseNew\tempdb.mdf');

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'D:\DataBaseNew\templog.ldf');

可以通过如下语句批量获取多个（若有）tempdb数据文件更改：

select name,'ALTER DATABASE tempdb MODIFY FILE  (NAME= '+name+' , FILENAME= '''+REPLACE(physical_name,'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\','D:\tempdbdata\') +''')'from sys.master_files where database_id=db_id('tempdb')

输出结果：

ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev , FILENAME= 'F:\db\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME= templog , FILENAME= 'F:\db\templog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp2 , FILENAME= 'F:\db\tempdb_mssql_2.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp3 , FILENAME= 'F:\db\tempdb_mssql_3.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp4 , FILENAME= 'F:\db\tempdb_mssql_4.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp5 , FILENAME= 'F:\db\tempdb_mssql_5.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp6 , FILENAME= 'F:\db\tempdb_mssql_6.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp7 , FILENAME= 'F:\db\tempdb_mssql_7.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp8 , FILENAME= 'F:\db\tempdb_mssql_8.ndf')

3、重启数据库服务

 

tempdb数据库因重启数据库服务后自动重建，因此对应不需要在第2步操作完成后实际迁移数据文件（原数据文件不会被自动删除，因此需要手动在重启服务后删除原位置下tempdb数据文件）

 

 参考：

https://www.cnblogs.com/moonliu/p/14261829.html