如何解決tempdb 增長過大的問題

How to resolve tempdb large file problem

Sometimes,we may encounter this problem that tempdb data or log file increases so fast that
disk space is not enough for file auto growth. Usually, We install SQL Server 2000 to C Disk on Server.
We could see the location of tempdb data and log file is

 ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data’. 

The file autogrowth is by 10 percents default. When the file is very large, it may cause user operation
become very slowly even time out when auto growing.

Some solutions:

1: Restart SQL Server,then it will release the source because tempdb will be
rebuilt when restart SQL Server. But the data in tempdb will lose, so if there exists a lot of data,we
should restart SQL Server after backup user database.

2: add a new file to another disk.

Alter database tempdb add file ( NAME='tempdata1', FILENAME ='d:\SQLDATA\tempdb_data1.ndf')
-- adding data file
Alter database tempdb add log file ( NAME='templog1', FILENAME ='d:\SQLDATA\tempdb_log_1.ldf')
-- adding log file

3: move tempdb data file and log file to another disk,then shrink them periodically,I think this is the best way.
The script. of moving file is:
Use master
Alter database tempdb modify file (NAME='TEMPDEV',FILENAME='D:\SQLDATA\TEMPDB.MDF'); 
Alter database tempdb modify file (NAME='TEMPLOG',FILENAME='D:\SQLDATA\TEMPLOG.LDF'); 

 Then restart the SQL Server Service, you will find that tempdb data and log file are in the new directory.You can delete the old files

later.
Last,we need to create a Job to shrink tempdb data and log file. But the frequency should be right,or it will affect server performance.The

rcommended time is off-production or weekend . The script. is: 

use tempdb
dbcc shrinkfile('tempdev')
use tempdb
dbcc shrinkfile('templog')