SQL 2012 tempdb not shrinking

We have a SQL 2012 Standard server running on Windows 2012 R2 Standard. Between 6 databases (production and test), the files add up to 76GB. However tempdb grew to 131GB. I ran shrink database from SQL Management Studio and the size decreased to 122GB but it will not shrink any more although available free space is 99%.

I have tried shrink database, shrink files, dbcc freeproccache. None of the database is in read-only mode.

What else can I do to shrink this or identify why it is large?

Thank you.
realtimerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You can't safely shrink tempdb while SQL is running.  It could crash the instance.

You can adjust tempdb size so that it is reset when SQL Server (stops and re-) starts.

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 19GB /*or whatever size you want*/ )
--ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev2, SIZE = 19GB /*or whatever size you want*/ ) ...

The next time SQL starts, it should resize tempdb.  To be absolutely sure, you could also add commands to a SQL start up proc to shrink tempdb (since these commands run as soon as SQL starts, it is safe to run them then).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
In addition to comment above - tempdb is recreated every-time SQL Server Service restatrs therefore you need to check and set the best initial file size, location and number of tempdb files as per suggestions from links below:

https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
http://www.brentozar.com/sql/tempdb-performance-and-configuration/

Then whenever is possible restart SQL Server service and it will be all in place an manageable for you.
0
realtimerAuthor Commented:
Forgot to mention that I also restarted SQL server service.
0
Brian CroweDatabase AdministratorCommented:
TempDB is designed to do a checkpoint at 70% of capacity on its own and there's nothing you can do to change this.  The only reason it would not perform this function is if there is an open transaction preventing the checkpoint.  Once the tempdb log file reaches 100% it will either grow if that is enabled or essentially it will become unresponsive and your instance will come to a halt.

To check on this.

USE tempdb;
GO

DBCC OPENTRAN

Open in new window


If you run the opentran multiple times over several seconds and the processid never changes then that is the transaction that is blocking the checkpoint.  If you kill that transaction the tempdb will perform a checkpoint and return to 0% usage but the file size doesn't change just how much of the file is being used.

Use this script to check log file internal usage

DBCC SQLPERF (LOGSPACE)

Open in new window


Restarting the SQL Server (<instance name>) service should reset the tempdb mdf and ldf files to their default sizes as defined in the  database properties.
0
realtimerAuthor Commented:
Thanks. I was able to shrink it with Alter database command and service restart.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.