Cannot shrink tempdev in tempdb

Question

Post reply

Cannot shrink tempdev in tempdb

sgambale

SSCommitted

Points: 1709
More actions
January 11, 2010 at 7:20 am

#140557

I cannot shrink tempdev data file in tempdb even though almost all the space is unallocated:
database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
tempdb 5878.50 MB 5876.45 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
1592 KB 688 KB 728 KB 176 KB
I want to get it to 2000 MB
initial size is under 100 mb.
I tried backup log to no_log, dbcc chrinkfile (tempdev, 2000)
there is nothing running currently in tempdb either.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgambale SSCommitted Points: 1709 More actions · Answer 1

I also did dbcc shrinkdatabase(tempdb)

and dbcc shrinkfile (tempdb, 2000, notruncate)

then did a truncateonly

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

sgambale (1/11/2010)
I cannot shrink tempdev data file in tempdb even though almost all the space is unallocated:
database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
tempdb 5878.50 MB 5876.45 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
1592 KB 688 KB 728 KB 176 KB
I want to get it to 2000 MB
initial size is under 100 mb.
I tried backup log to no_log, dbcc chrinkfile (tempdev, 2000)
there is nothing running currently in tempdb either.

Ohh...I'm not sure,

Why u want shrink the tempdb.

Please don't shrink the tempdb some times it ll cause the corruptions.

How the backup ll work ?

what command did u use?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

sgambale (1/11/2010)
I also did dbcc shrinkdatabase(tempdb)
and dbcc shrinkfile (tempdb, 2000, notruncate)
then did a truncateonly

First y u want shrink it,5Gb is not big one.

tempdb 5878.50 MB 5876.45 MB

Read the MS KB-307487

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

sgambale SSCommitted Points: 1709 More actions · Answer 4

I understand 5 GB is not big.

I just took responsibility for the SQL Server from another group and there is a script that monitors database size to protect from runaway queries or log size becoming very large for all databases.

I raised the limit to which the script will alert us. So the alerting has now stopped.

But just as something to understand, why would it not shrink?

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 5

There is a chance there could have been a hung connection. Also depending on what you were doing to find out that there was no-one in the DB (sp_who2) for example, you may have been creating a tempdb object.

The best way (IMHO) to shrink tempdb is to stop and start the service.

Ramji29 Hall of Fame Points: 3832 More actions · Answer 6

Just a addition to this you should always set a inital size for temp db so it will not auto grow again n again.