临时数据库TempDB 虽然是临时库，但对整个数据库系统性能却起到很关键的作用；平时用到的中间数据集会暂时保存到TempDB 中，比如：临时表，排序，临时统计信息，一些中间结果数据，索引重建 等。我们经常发现客户的TempDB 数据库暴涨，业务量大时性能有所下降；对于业务量少的实例上的数据库默认安装即可；对于业务量大的实例请安装人员按照下面的统一配置安装；
下面的SQL 脚本详细的介绍了 tempdb数据库 迁移原来的文件和增加新的文件的详细步骤：
/**01 把Tempdb 文件转移到高速磁盘 或者 SSD 盘；**/ USE master; GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = ‘Z:\Data\tempdb.mdf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = ‘Z:\Log\templog.ldf‘); GO --重启SQLSERVER 服务; --根据CPU 的数量增加Tempdb 的文件数量；如果CPU 的数量过多我们一般分6 – 8 个即可； USE [master] GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev02‘, FILENAME = N‘Z:\Data\tempdev02.ndf‘ , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev03‘, FILENAME = N‘Z:\Data\tempdev03.ndf‘ , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev04‘, FILENAME = N‘Z:\Data\tempdev04.ndf‘ , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev05‘, FILENAME = N‘Z:\Data\tempdev05.ndf‘ , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev06‘, FILENAME = N‘Z:\Data\tempdev06.ndf‘ , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev07‘, FILENAME = N‘Z:\Data\tempdev07.ndf‘ , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N‘tempdev08‘, FILENAME = N‘Z:\Data\tempdev08.ndf‘ , SIZE = 1048576KB , FILEGROWTH = 131072KB ) GO /***02 整体迁移tempdb*****/ USE master; GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev‘, FILENAME = N‘Z:\Data\tempdb.mdf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev02‘, FILENAME = N‘Z:\Data\tempdev02.ndf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev03‘, FILENAME = N‘Z:\Data\tempdev03.ndf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev04‘, FILENAME = N‘Z:\Data\tempdev04.ndf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev05‘, FILENAME = N‘Z:\Data\tempdev05.ndf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev06‘, FILENAME = N‘Z:\Data\tempdev06.ndf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev07‘, FILENAME = N‘Z:\Data\tempdev07.ndf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘tempdev08‘, FILENAME = N‘Z:\Data\tempdev08.ndf‘); GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = N‘templog‘, FILENAME = N‘Z:\Log\templog.ldf‘); GO
SQL SERVER 临时数据库 tempdb 迁移或增加文件
原文：https://www.cnblogs.com/lvzf/p/10571275.html