asked on

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

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.

realtimer

ASKER

Forgot to mention that I also restarted SQL server service.

Brian Crowe

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.

realtimer

ASKER

Thanks. I was able to shrink it with Alter database command and service restart.