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

TEMP数据库文件迁移

环境：TEMP数据库过大（默认数据文件存放于C盘）

单机环境下操作比较简单，仅需要重启即可重建tempdb数据库；

AlwaysOn群集环境同样，重启服务即可重建本机节点tempdb数据库；

但以上操作指标不治本。

为彻底解决temp数据库过大问题（实质是tempdb数据库默认存放在C盘，而一般服务器C盘空间有限），我们需要将tempdb数据库转移位置放在其他盘符。

本次主要处理操作为更改数据库tempdb数据文件位置

 

处理操作如下：

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

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

 

本文中假定数据结果如下

name	physical_name
tempdev	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
templog	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf
temp2	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf
temp3	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf
temp4	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf
temp5	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_5.ndf
temp6	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_6.ndf
temp7	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_7.ndf
temp8	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_8.ndf

新存放tempdb数据路径如下

D:\tempdbdata

 

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= 'D:\tempdbdata\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME= templog , FILENAME= 'D:\tempdbdata\templog.ldf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp2 , FILENAME= 'D:\tempdbdata\tempdb_mssql_2.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp3 , FILENAME= 'D:\tempdbdata\tempdb_mssql_3.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp4 , FILENAME= 'D:\tempdbdata\tempdb_mssql_4.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp5 , FILENAME= 'D:\tempdbdata\tempdb_mssql_5.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp6 , FILENAME= 'D:\tempdbdata\tempdb_mssql_6.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp7 , FILENAME= 'D:\tempdbdata\tempdb_mssql_7.ndf')
ALTER DATABASE tempdb MODIFY FILE (NAME= temp8 , FILENAME= 'D:\tempdbdata\tempdb_mssql_8.ndf')

 

3、重启数据库服务。

 

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