July 16, 2017 at 3:36 am
Hello All,
I tried to shrink my tempdb data files but didn't succeeded.
i got this message: "DBCC SHRINKFILE: Page 1:34383801 could not be moved because it is a work table page."
i used this script:
CHECKPOINT;GODBCC DROPCLEANBUFFERS;GO
DBCC FREEPROCCACHE;GO
DBCC FREESYSTEMCACHE ('ALL');GO
DBCC FREESESSIONCACHE;GO
DBCC SHRINKFILE (TEMPDEV, 20480); --- New file size in MB
GO
https://sqlsunday.com/2013/08/11/shrinking-tempdb-without-restarting-sql-server/
Can you please help me?
i can't restart the service.
Thanks in advance!!!
July 16, 2017 at 3:32 pm
Aaron says it better than I can:
https://dba.stackexchange.com/questions/73324/tempdb-will-not-shrink-no-open-transactions
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
July 17, 2017 at 1:34 am
Thanks for the quick response and this great article.
July 17, 2017 at 8:54 am
select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0
See what has allocated pages and not deallocated pages.
If DBCC OPENTRAN also shows nothing it could be a deferred transaction causing the issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply