unable to shrink tempdb

by Banu AkkĊŸ   Last Updated March 05, 2018 08:06 AM

My production server environment is Microsoft SQL Server 2014 (SP2-CU10-GDR). I split my tempdb files into 8 files (I have 8 CPU ) One day, my tempdb size 720 GB (each of file 90GB *8 ) I need to shrink these files than I run that command.

checkpoint
go
USE [tempdb]
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO 

But I couldn't shrink tempdb. I restart that server and tried again but result is the same. My error message is :

Time-out occurred while waiting for buffer latch type 3 for page (7:313128), database ID 2.

I try to detect table that causes this issue.

DBCC TRACEON(3604)
DBCC PAGE('tempdb',7,313128,1) WITH TABLERESULTS

Result is : Metadata: ObjectId = 0

I need to shrink these files to gain 720 GB. Any help on improving it will be appreciated!



Related Questions




TempDB data files don't shrink well on SQL 2008

Updated April 03, 2018 08:06 AM


DBCC Refuses to Shrink a Data File - drive was full

Updated March 06, 2018 01:06 AM