November 28, 2017 at 10:17 pm
Hi All,
I have 3 ndf files for Tempdb and each one is now grown to 110+ GB. i.e total 330 GB+
I have tried
use tempdb
go
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,10024)
GO
DBCC SHRINKFILE (templog,10024)
GO
DBCC SHRINKFILE (Tempdb_Second,10240)
GO
DBCC SHRINKFILE (TempDb_third,10240)
GO
However I am getting following errors and TempDB do not shrink to Original size. I have even tried restarting the SQL service, but I am still getting the following error and TempDB not shrink at all after restart.
Msg 5054, Level 16, State 1, Line 1
Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle.
Has anyone faced similar issue? What is the cause of this error and How to rectify it? Also let me know how can I find the query which cause such file growth. ?
November 29, 2017 at 12:11 am
Jatin Soni - Tuesday, November 28, 2017 10:17 PMHi All,I have 3 ndf files for Tempdb and each one is now grown to 110+ GB. i.e total 330 GB+
I have tried
use tempdb
go
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,10024)
GO
DBCC SHRINKFILE (templog,10024)
GO
DBCC SHRINKFILE (Tempdb_Second,10240)
GO
DBCC SHRINKFILE (TempDb_third,10240)
GO
However I am getting following errors and TempDB do not shrink to Original size. I have even tried restarting the SQL service, but I am still getting the following error and TempDB not shrink at all after restart.Msg 5054, Level 16, State 1, Line 1
Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle.
Has anyone faced similar issue? What is the cause of this error and How to rectify it? Also let me know how can I find the query which cause such file growth. ?
Quick thought, have you looked into what is using the tempdb (directly and indirectly)?
One option is to bring the server up in single user mode or drop all connections to the server before attempting to resize the files.
😎
Question, why 3 files, bit of an odd number?
November 29, 2017 at 1:54 am
Looking at tempdb usage is the right place to start.
3 tempdb files that size, I presume you have a lot of data? One thought, perhaps you have an index maintenance operation running?
Because the operation is taking so long, the IAM pages are still being calculated at the time you are trying to shrinkfile.
Once resolved, perhaps look to have more tempdb files? Last time I checked, best practice was 1 tempdb file per core, up to 8.
December 4, 2017 at 4:28 am
https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server
CHECKPOINT;
GO
-- Clean all buffers and caches
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
GO
-- Now shrink the file to your desired size
DBCC SHRINKFILE (TEMPDEV, 300);
-- Make sure that there is no running transaction which uses the tempdb while shrinking!
-- This is most trickiest part of it all.
GO
1 file per core has been debunked a while back by Paul Randall Myth Busters.
4 Files and then observe and monitor TEMPDB too see if it requires more.
Shrinking TEMPDB can cause something which looks like corruption in TEMPDB but it is not actually corruption.
Actual corruption has not been a problem since SQL 2000
https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/
I would run at quiet periods if possible.
See if any pages have been allocated in tempdb currently
--------------------------------------------------------------
select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0
-------------------------------------------------------------------
;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), ''
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC
December 4, 2017 at 4:39 am
Also, ensdure that you shrink the files to the same size. If you have them at different sizes, SQL Server won't make proper use out of them. In your above script you are shrinking 1 file to 10024KB, and the other 2 to 10240KB.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 5, 2017 at 4:28 am
Thanks Everyone. Tempdb is now shrunk to original size after my IT team rebooted the server. somehow when I have restarted the service, it had not work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply