when i run the DBCC SHRINKFILE i get this error message DBCC SHRINKFILE: Page 1:3740312 could not be moved because it is a work table page. but the free space on the tempdb is 29GB. how can i know what is keeping my tempdb busy,so i can't shrink it? sp_spaceused = database_name database_size unallocated space -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ tempdb 29971.25 MB 29219.73 MB reserved data index_size unused ------------------ ------------------ ------------------ ------------------ 1560 KB 648 KB 712 KB 200 KBTHX
You can't shrink tempb in working hours. Tempdb reduce his space to minimum each time you down sql service. Also if tempdb need to grow you have to have plenty space for that. May be you have other drive and then I suggest to create other filegroup in tempdb in that drive.
Why are you shrinking TEMPDB? Any space issues on the drive that is located, see this http://sqlserver-qa.net/blogs/perftune/archive/tags/tempdb/default.aspx to resolve certain TEMPDB issues.
I've to shrink it because someone make an incorrect query and it fill up the tempdb. know no one is working on the tempdb,but i still can't shrink it to lets say 10GB(now it's 30GB). what can i do to shrink it beside of restart the sql server service? THX
If there are any other open processes or existing executio of queries then you may not be able to shrink it efficiently, in this case look for a maintenance window to accomplish the task.