November 20, 2012 at 3:36 am
In one of our servers tempdb is grown to 75 gb. If we check properties of tempdb it is showing 73 gb free, but when we try to shrink tempdb it is giving below given error.... Has anybody faced similar issue and resolved..
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.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 20, 2012 at 3:54 am
Restart the engine tempdb drop and recreate it self
November 20, 2012 at 3:54 am
Thanks John...
We I have already tried dbcc shrinkdatabase and shrinkfile and both are giving same error. This being our production server we are looking alternatives without restarting SQL service.
November 20, 2012 at 3:56 am
nitin.doshi (11/20/2012)
In one of our servers tempdb is grown to 75 gb. If we check properties of tempdb it is showing 73 gb free, but when we try to shrink tempdb it is giving below given error.... Has anybody faced similar issue and resolved..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.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
what is log file size of tempdb out of 75 GB
-----------------------------------------------------------------------------
संकेत कोकणे
November 20, 2012 at 4:03 am
75 GB is purely data file and size of log is 3.5 gb
November 20, 2012 at 4:30 am
can you post here, your databases configurations (their size ) ? additionally also check if there are any open transactions or not ? tempdb handles alot of stuff like temp table .temp valriable .order by grout by ..work table . index rebuils (sort in temp db ) etc etc etc .. so its natural that it grew upto its limits
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 20, 2012 at 4:31 am
If you are trying to run a shrink on the tempdb you must be in single user mode and ensure no other activity is occurring in the tempdb database. If there is any activity on the tempdb then the DBCC SHRINKFILE and DBCC SHRINKDATABASE Commands shall fail.
If you are doing this you must ensure that you follow the recommendations from Microsoft located at http://support.microsoft.com/kb/307487
There is also a support article for SQL Server 2008 which states the same as the above support article, but I don't have it to hand
November 20, 2012 at 4:39 am
nitin.doshi (11/20/2012)
This being our production server we are looking alternatives without restarting SQL service.
Leave it as is?
If tempDB grew that size, then, unless there was some unusual activity, it suggests that TempDB needs to be that size and perhaps the initial size for the DB should be set closer to what it currently is.
As per the kb article posted earlier, shrinking an in-use TempDB can cause corruptions that will force a restart. The only safe way to shrink TempDB is to completely quiesce the server (no activity of any form), that usually means a restart.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2012 at 4:39 am
You also need to understand what is using tempdb. Shrinking the file may sort the problem sort term but does not mean this shall not happen again
SELECT TOP 10 session_id, database_id, user_objects_alloc_page_count + internal_objects_alloc_page_count / 129 AS tempdb_usage_MB
FROM sys.dm_db_session_space_usage
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC;
The above query shall tell what is using tempdb.
November 20, 2012 at 5:04 am
Thanks Gail.
Tempdb original size was 10 GB and was running without any issue with this size till last week.
We are having 100 databases on this box, and One of our team member set read_committed_snapshot on a database. While purging records from heavily used table in this database, we found that tempdb is growing rapidly hence the session was killed and read_committed_snapshot was set off, but tempdb size which is grown from 10 gb to 75 gb, we are not able to revert.
Currently there are no open transactions..
November 20, 2012 at 6:16 am
Restart the SQL Server. As has been pointed out more than once, you cannot safely shrink an in-use TempDB, you need to completely and totally quiesce the server (no operations, no connections, no queries) and that pretty much means a restart (at which point you wouldn't need to shrink because TempDB goes back to configured size upon restart)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply