473,554 Members | 2,639 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Temp DB growing to max available disk space

I've written a SP which does some complex calculations and in the end
dumps data into 2 tables (master & detail) When I run this sp for
smaller no of IDS (employees i.e for 13000 in Master and 60000 records
in detail table) it takes around 3-4 hrs and if I run for all
employees in the database (i.e. abt 60000 records in master and 180000
records in detail table) then it takes around 10hrs to complete.

I'm using temp table to hold data and then do the calculations, but
sometimes when I run the SP temp db starts growing and reaches up to
25 GB and the process fails as there is no space left on the disk, and
lately I'm not able to run the SP for every employee, I had to end the
process after 16 hrs

If anybody can guide me what could be posible resons or where I should
look for solution.

My row size in master table is arounnd 2000 bytes and in detail table
abt 300 bytes.

Thanks in advance.

Subodh
Jul 20 '05 #1
3 2002
Hi

To shrink tempdb

http://support.microsoft.com/default...;en-us;Q307487

As far as why you are having problems is unclear because you have not posted
the code, reducing the number of temporary tables would help, but how this
can be obtained would require the source code.
The best solution may even be to take this out of the database. You should
also make sure that you do not store unnecessary information in your
temporary tables.

John

"Subodh" <sg****@agline. on.ca> wrote in message
news:90******** *************** ***@posting.goo gle.com...
I've written a SP which does some complex calculations and in the end
dumps data into 2 tables (master & detail) When I run this sp for
smaller no of IDS (employees i.e for 13000 in Master and 60000 records
in detail table) it takes around 3-4 hrs and if I run for all
employees in the database (i.e. abt 60000 records in master and 180000
records in detail table) then it takes around 10hrs to complete.

I'm using temp table to hold data and then do the calculations, but
sometimes when I run the SP temp db starts growing and reaches up to
25 GB and the process fails as there is no space left on the disk, and
lately I'm not able to run the SP for every employee, I had to end the
process after 16 hrs

If anybody can guide me what could be posible resons or where I should
look for solution.

My row size in master table is arounnd 2000 bytes and in detail table
abt 300 bytes.

Thanks in advance.

Subodh

Jul 20 '05 #2
Thanks for the reply John, the code itself is 900 lines and has
complex business logic behind it. That is the reason I did not post
the code. I'm droping the temp tables within the code as soon as their
job is finished. I've abt 25 non-indexed views on those tables, do u
think those can make the performance of my SP slow. And the thing is
the temp db does not grow to 25GB every time I execute my SP, it only
happenes sometimes.
And when whenever the SP is executed in 10-12 hrs, temp db remains
under 1GB.

Can you explain more on "best solution may even be to take this out
of the database".

Thanks,

Subodh

"John Bell" <jb************ @hotmail.com> wrote in message news:<41******* *************** *@news.easynet. co.uk>...
Hi

To shrink tempdb

http://support.microsoft.com/default...;en-us;Q307487

As far as why you are having problems is unclear because you have not posted
the code, reducing the number of temporary tables would help, but how this
can be obtained would require the source code.
The best solution may even be to take this out of the database. You should
also make sure that you do not store unnecessary information in your
temporary tables.

John

"Subodh" <sg****@agline. on.ca> wrote in message
news:90******** *************** ***@posting.goo gle.com...
I've written a SP which does some complex calculations and in the end
dumps data into 2 tables (master & detail) When I run this sp for
smaller no of IDS (employees i.e for 13000 in Master and 60000 records
in detail table) it takes around 3-4 hrs and if I run for all
employees in the database (i.e. abt 60000 records in master and 180000
records in detail table) then it takes around 10hrs to complete.

I'm using temp table to hold data and then do the calculations, but
sometimes when I run the SP temp db starts growing and reaches up to
25 GB and the process fails as there is no space left on the disk, and
lately I'm not able to run the SP for every employee, I had to end the
process after 16 hrs

If anybody can guide me what could be posible resons or where I should
look for solution.

My row size in master table is arounnd 2000 bytes and in detail table
abt 300 bytes.

Thanks in advance.

Subodh

Jul 20 '05 #3
Hi

Taking the process outside the database refers to either putting your
business logic into a middle tier or using a separate process. This
may reduce the need to create these temporary tables as you could
"scroll" through a result set and perform the necessary calculations
in code.

You may also want to see if using derived tables will remove the need
for the temporary tables.

You can get contention on the tempdb system tables if you have
multiple processes creating temporary tables at the same time. You may
want to run profiler and monitor blocking whilst this process is
running.

John

sg****@agline.o n.ca (Subodh) wrote in message news:<90******* *************** ****@posting.go ogle.com>...
Thanks for the reply John, the code itself is 900 lines and has
complex business logic behind it. That is the reason I did not post
the code. I'm droping the temp tables within the code as soon as their
job is finished. I've abt 25 non-indexed views on those tables, do u
think those can make the performance of my SP slow. And the thing is
the temp db does not grow to 25GB every time I execute my SP, it only
happenes sometimes.
And when whenever the SP is executed in 10-12 hrs, temp db remains
under 1GB.

Can you explain more on "best solution may even be to take this out
of the database".

Thanks,

Subodh

"John Bell" <jb************ @hotmail.com> wrote in message news:<41******* *************** *@news.easynet. co.uk>...
Hi

To shrink tempdb

http://support.microsoft.com/default...;en-us;Q307487

As far as why you are having problems is unclear because you have not posted
the code, reducing the number of temporary tables would help, but how this
can be obtained would require the source code.
The best solution may even be to take this out of the database. You should
also make sure that you do not store unnecessary information in your
temporary tables.

John

"Subodh" <sg****@agline. on.ca> wrote in message
news:90******** *************** ***@posting.goo gle.com...
I've written a SP which does some complex calculations and in the end
dumps data into 2 tables (master & detail) When I run this sp for
smaller no of IDS (employees i.e for 13000 in Master and 60000 records
in detail table) it takes around 3-4 hrs and if I run for all
employees in the database (i.e. abt 60000 records in master and 180000
records in detail table) then it takes around 10hrs to complete.

I'm using temp table to hold data and then do the calculations, but
sometimes when I run the SP temp db starts growing and reaches up to
25 GB and the process fails as there is no space left on the disk, and
lately I'm not able to run the SP for every employee, I had to end the
process after 16 hrs

If anybody can guide me what could be posible resons or where I should
look for solution.

My row size in master table is arounnd 2000 bytes and in detail table
abt 300 bytes.

Thanks in advance.

Subodh

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5919
by: Bob | last post by:
Hello all, I'm attempting to use JpGraph (http://www.aditus.nu/jpgraph/index.php) with PHP (4.3.1) and Apache (1.3.26) runnning on WindowsXP. However, I receive the following notice: Notice: Undefined index: TEMP in c:\apache\htdocs\cprgssp\php\lib\jpgraph\jpgraph.php on line 189 Lines 187-193 of jpgraph.php are:
3
6385
by: shumaker | last post by:
When I import data I first import it from a text file into a table of it's own, then using some logic insert some of the records into a permanent table. I am considering having the table that the data from the text file is placed in being there all the time and just clearing it out after I do the import, or creating it and after using it...
3
826
by: Lamont | last post by:
VB.NET Can anyone tell me how I can get the Drive Space information from a remote computer on the same domain and physical network? Primarily I need the Total disk space and space used or free (or both). -- Lamont - OKC Developer
3
4273
by: Esger Abbink | last post by:
Hello, it is very possible that this is a well described problem, but I have not been able to find the solution. On two production server (7.2rc2) of ours the data directory is growing to very large sizes while the data that is actually in the db's isnt 1. that large and 2. growing. The databases see a fairly limited/constant use at...
1
10460
by: airmax | last post by:
Hello DBA’s I have a problem with my TEMP tablespace. It’s 1 GB and its full. Shouldn’t be empty? When the tablespace is full i have to restart the database in order clean up the tablespace, but this is not a solution!!! I don’t want to extend it becuase it will be full again. In my previous job the table space was 800MB and it was always...
4
13213
by: yashgt | last post by:
Hi, We have created a SQL server 2000 database. We observe that the transaction log keeps growing over time. We are now about to run out of space. We have been periodically shrinking the database. Nevertheless the size has increased. I would imagine that a transaction log can be eliminated if we stop the database. Can that be done? Is there...
0
2102
by: elias.farah | last post by:
Hello Everyone, I've been searching for a few hours on how to check the available disk space (on a Flash disk actually) from within MS-Access. Basically, I want to ensure there is adequate room on the Flash drive before a Backup is attempted. I've been trying to get Doug's code working within Access 2007 (and Vista x64), but it doesn't...
3
1812
by: bgold12 | last post by:
I'm curious about how websites deal with the problem of continuously growing data. For example, there are many forums that preserve all posts for many years back, and are continuously receiving and storing new posts all the time. No website has access to an infinite amount of storage space, so: What kind of buffer (i.e. space available) do...
3
2597
by: MC | last post by:
I'm developing an application that needs a large (up to 4 GB) amount of temporary file space on a local (not network-attached) disk. What are some good tactics to use in a C# program to make sure the swap space is suitable? There can be some interaction with the user to choose the swap file location, but the more the program can find out about...
0
7615
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7819
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8055
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7897
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5446
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3589
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3566
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2030
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1149
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.