ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何解决tempdb 增长过大的问题

如何解决tempdb 增长过大的问题

原创 Linux操作系统 作者:tolywang 时间:2009-08-12 15:02:51 0 删除 编辑


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') 

 

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/35489/viewspace-612444/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    14695514