question

yunusemre avatar image
yunusemre asked

tempdb database free space is very low

I have a 47GB Tempdb database and only 10MB free space. How can I increase free space of tempdb

performancetempdbsql-server-2014shrink-databasetempdbfiles
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Elysian avatar image
Elysian answered

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,

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It might also worth be worth a mention that shrinking the temp file may only be a temporary measure. It may grow to that size again so it might be worth sticking some type of monitoring solution together to see whats causing it to grow.

0 Likes 0 ·
yunusemre avatar image
yunusemre answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Elysian avatar image
Elysian answered

@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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.