SQL server tempdb expansion,shrinking

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?


Thanks
crazywolf2010Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachitjainCommented:
TempDB gets filled up with many things:
1.> Temp tables
2.> Large table variables
3.> Intermediate query results during complex queries
4.> image of objects if you have enabled snapshot isolation level for your database/server
5.> Cursor processing..
6.> Other metadata

To control the size of tempdb you need to observe usage of all these features in your BCP program. Might be possible, there is some performance issue with your queries in BCP load process or you are using temp tables excessively.
0
sachitjainCommented:
Tuning your complex queries either by breaking them into pieces or by adding more indexes could actually improve the performance and reduce load on tempdb.
0
crazywolf2010Author Commented:
Could you please answer to my specific queries below.

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?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Anthony PerkinsCommented:
Does tempdb files shrink automatically after the transaction using them has finished?
No.  You cannot set Tempdb to Auto-Shrink.
0
crazywolf2010Author Commented:
OK that's great! So the question is, why did the tempdb didn't expand when it is set for autogrow and lot of space was free on the volume.

Is there any DMV which will show growth of tempdb file?

Thanks
0
sachitjainCommented:
Yes it would shrink automatically once the transaction gets over. That's how it works. If your batch process failed last night saying short of space at tempdb then this only would be the reason. Try looking into current SQL Server log (Server->Management->SQL Server Logs->Current) on to your SQL server to check if you find anything regarding this error there.

Following link from MSDN could help you monitoring and troubleshooting insufficient disk space with tempdb.
http://msdn.microsoft.com/en-us/library/ms176029.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Yes it would shrink automatically once the transaction gets over
Care to post some documentation to back that up?  As I stated previously, you cannot set the tempdb to Auto-Shrink.
0
sachitjainCommented:
Agreed acperkins!

I think I wrote wrong statement earlier. There are 2 things:
1.> Size of tempdb
2.> Free space in tempdb

With end of each transaction, space allocated to internal objects of that transaction within tempdb would be released thus free space in temp db would increase.
Size you are right, it won't change automatically until we restart SQL server or shrink explicitly by dbcc shrinkdatabase or shrinkfile command. Somehow I messed up between free space and size. I apologize about that.
0
Scott PletcherSenior DBACommented:
>> have a BCP load process which failed last night saying short of space at tempdb. <<

Please post the *exact* error message(s).

And review the SQL Server error log; you very likely have additional error msgs there.

Verify that the tempdb *log* file is on a drive with additional space available, and that it is set to autogrow, and set to a fixed amount vs a percentage amount (such as 100M rather than 10%).
0
SJCFL-AdminCommented:
If the BCP failed I expect it would have initiated a rollback reversing any changes to the temp database ....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.