Why the log file is growing too much upon running nested stored procedures (MS SQL 2K)

[sasachi sachi sachi]

Hi there,

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.

Any ideas would be greatly appreciated..

Thanks in advance
*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

sasachi sachi sachi (sa*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx