question

Hi ,

First, why would you like to shrink your tempdb..it might effect currently running queries

Sometimes if there is no free space left inside tempdb it can worse the performance of other databases too by denying temporary object creation of other databases.

1) If you can arrange production downtime then go with restart option it will clean the tempdb files.

2) Otherwise , your can run below command to free up the tempdb files but do not shrink below used space

use tempdb --

Show Size, Space Used, Unused Space, and Name of all database files select [DBFileName] = a.name, [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) from sysfiles a

-- by checking used space try to shrink using below command

DBCC SHRINKFILE('tempdev',500) -- here 500 is in MB and it is an example

GO

However this query will not work if some transaction hold up your tempdb

3) If above two wont work for you then go with below query but it might worse the performance of queries..you have to inform users about it

use tempdb

GO

Checkpoint

GO

DBCC FREEPROCCACHE -- clean cache

DBCC DROPCLEANBUFFERS -- clean buffers

DBCC FREESYSTEMCACHE ('ALL') -- clean system cache

DBCC FREESESSIONCACHE -- clean session cache dbcc shrinkfile ('tempdev',1024) -- shrink db file dbcc shrinkfile ('templog') -- shrink log file

GO

Thanks,

thank you WRBI. I will restart to instance and search the reasons that increasing tempdb size

@WRBI- Yes I strongly Agree !!...its good to find a root cause of it. If the behavior of data file is same then you may also go with increasing the disk space. It means there is load on ur production server which is demanding tempdb to grow.