I have a 47GB Tempdb database and only 10MB free space. How can I increase free space of tempdb
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
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
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
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.
19 People are following this question.