Temp DB growing to max available disk space

[Subodh]

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

[John Bell]

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.google.c om...

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

[Subodh]

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.google.c om...

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

[John Bell]

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.on.ca (Subodh) wrote in message news:<90**************************@posting.google. 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.google.c om...

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