troubleshooting Question

can you see the blocking process on a shrink on TempDB

Avatar of 25112
25112 asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
33 Comments7 Solutions2759 ViewsLast Modified:
the tempdb data file is 5GB. currently only 20MB used. we need to shrink this to 1GB as it is a small database.

(the programmer code had a problem - was mistakenly inserting and deleting in a loop and hence the log became too big- this is fixed now)

the shrink does not happen with the DBCC command.

I looked at
http://technet.microsoft.com/en-us/library/ms189493.aspx for troubleshooting ideas:
*There is enough space on disk
*select count(1) from sys.dm_tran_active_snapshot_database_transactions is 0

Is it possible with SQL Server DMVs or other in-built tools to find out exactly what is occupying tempdb or blocking a shrink

select count(1) from master..sysprocesses where dbid = db_id ('tempdb') is also zero.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 7 Answers and 33 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 7 Answers and 33 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros