troubleshooting Question

SQL server tempdb expansion,shrinking

Avatar of crazywolf2010
crazywolf2010Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
10 Comments1 Solution569 ViewsLast Modified:
Hi All,
I have a BCP load process which failed last night saying short of space at tempdb.
When I had a look this morning I have more than 120 GB free on the volume where tempdb is located. The total size of volume is 300GB.

The files on tempdb device are still of size 10GB. I configured them with "unrestricted Growth" so they should take remaining 120GB free when needed.

My question is "Why do I have an error in the first place"?
Does tempdb files shrink automatically after the transaction using them has finished?

Can I use any DMV to locate space usage for last 24 hours? If not how do I know what went wrong with last night's process?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 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 1 Answer and 10 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