Posted by Erland Sommarskog on 02/11/06 20:02

sasachi sachi sachi (sasachi@getmail.com) writes:
> I have a data manipulation process written in a Nested Stored procedure
> that have four levels deeper. When I run these individual procedures
> individually they all seems to be fine. Where as when I run them all
> together as Nested proces (calling one in another as sub-procedures) Log
> file is growing pretty bad like 25 to 30GB.. and finally getting kicked
> after running disk space. This process is running around 3hrs on a SQL
> serever Standard Box having dual processer and 2gb ram.
>
> This procedures have bunch of bulk updates and at least one cursor in
> eacch procedure that gets looped through.
>
> I was wondering if anybody experienced this situation or have any clue
> as to why is this happening and how to resolve this?
>
> I am in a pretty bad shape to deliver this product and in need of urgent
> help.

Unfortunately, you did not include any code, neither did you mention
anything about transactions etc.

So I will have to ask:

o In what recovery mode is the database in?

o What sort of recovery is needed for the application in case of a
failure? Do you need up-to-the-point recovery, or it is good enough
to restore the most recent backup?

o Do define any transactions within your procedures?

o How much data do you estimate that you update/insert?

o Would be possible for you to post the code.


All I can say at this point is that the nesting as such does not affect
how much the transaction log grows. But if a procedure updates a couple
of rows, and you call it 20000 times this has some importance.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]