Solved

SQL server tempdb expansion,shrinking

Posted on 2012-03-20
10
499 Views
Last Modified: 2012-04-10
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
0
Comment
Question by:crazywolf2010
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 12

Expert Comment

by:sachitjain
ID: 37741707
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
 
LVL 12

Expert Comment

by:sachitjain
ID: 37741746
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
 

Author Comment

by:crazywolf2010
ID: 37741827
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37742167
Does tempdb files shrink automatically after the transaction using them has finished?
No.  You cannot set Tempdb to Auto-Shrink.
0
 

Author Comment

by:crazywolf2010
ID: 37742204
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 12

Accepted Solution

by:
sachitjain earned 500 total points
ID: 37742276
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37742348
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
 
LVL 12

Expert Comment

by:sachitjain
ID: 37742386
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37742491
>> 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
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37744467
If the BCP failed I expect it would have initiated a rollback reversing any changes to the temp database ....
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now