Temp DB growing to max available disk space

**John Bell** · Jul 20 '05, 05:07 AM

Re: Temp DB growing to max available disk space

Hi

To shrink tempdb

http://support.microsoft.com/default.aspx?scid=kb;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" <sgoyal@agline. on.ca> wrote in message
news:90104bf0.0 410011010.16de1 c1b@posting.goo gle.com...[color=blue]
> 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[/color]

**Subodh** · Jul 20 '05, 05:07 AM

Re: Temp DB growing to max available disk space

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" <jbellnewsposts @hotmail.com> wrote in message news:<415dc804$ 0$26663$afc38c8 7@news.easynet. co.uk>...[color=blue]
> 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" <sgoyal@agline. on.ca> wrote in message
> news:90104bf0.0 410011010.16de1 c1b@posting.goo gle.com...[color=green]
> > 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[/color][/color]

**John Bell** · Jul 20 '05, 05:08 AM

Re: Temp DB growing to max available disk space

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

sgoyal@agline.o n.ca (Subodh) wrote in message news:<90104bf0. 0410041014.7620 9a4c@posting.go ogle.com>...[color=blue]
> 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" <jbellnewsposts @hotmail.com> wrote in message news:<415dc804$ 0$26663$afc38c8 7@news.easynet. co.uk>...[color=green]
> > 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" <sgoyal@agline. on.ca> wrote in message
> > news:90104bf0.0 410011010.16de1 c1b@posting.goo gle.com...[color=darkred]
> > > 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[/color][/color][/color]

Temp DB growing to max available disk space

Temp DB growing to max available disk space

Comment

Comment

Comment