Rollback in delete statements 1

[boligoma]

I have an third party aplication that runs in SQL 2000 a delete statment in a table and then an insert statement in the same table, the log file for the insert table is about 40 Mb every time it runs and in the same connection it runs about 40 times.

Before it runs every time I run a SP with the commands:

dbcc dropcleanbuffers
backup log BDNAME with truncate_only

With the second command I make sure that the maximum space in the log file is about 40 MB. Nevertheless, the process enter in a rollback every time I run it when the table is full in the delete statement. When the table is empty (I drop it and then I create it again) the process run fine.

When I run the Profiler it sends a warning telling that the memory for the process is full, but I have about 4GB of Ram Size in the computer.

Is there a way (instrunction, SP, whatever) to clean up the memory used by the same user id?? (it seems that dbcc dropcleanbuffers and backup log BDNAME with truncate_only don´t do much about it).

thanks,

 

[tlbroadbent]

Please post the exact error message. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[boligoma]

The application doesn't give an error, it just makes the rollback in the delete transaction and it doesn't recover after 6 hours.

When I run the Profiler trace it sends a "Profiler Warning" saying in the TextData field(in Spanish, I have the spanish version): "La información de la traza no se pudo modificar puesto que la memoria para el proceso esta llena". (The information of the trace could not be modified because the memory of the process is full).

 

[tlbroadbent]

OK, the message explains why the trace failed. FYI, unless you are using Enterprise Edition, SQL Server only uses 2GB of memory. The TRACE will have the amount of memory on the machine where you run it. You should send the trace result to a file rather than to the screen to avoid the out of memory problem.

This does not explain why the application transaction failed, unless you ran the trace on the server. You need to determine why the transaction is rolling back. Does the application keep an error log? Does the SQL error log contain any information about the process? Does the server application log contain any messages.

One more item to note, when you truncate the transaction log, you invalidate any transaction backups. You will need to performa full backup immediately after the process completes.

How big is the transactionlog for the database? How much space is available on the disk holding the log? If you can determine that the database transaction log is growing very large and filling the disk or if SQL Server indicates the transaction log is full, you may need to create more space on the disk or move the log to another disk with more space. You should then make the log very before the process starts and set the growth facotor to 40 MB or larger.

Ideally, if the application could be modified to commit transactions after the delete and each insert, the transaction log growth would not be so great. However, it is difficult to persuade software vendors to modify the process.

If the delete and all inserts are done in one transaction, and I suspect they are, the transaction log must grow to hold all uncommitted transactions. You can reduce the amount of growth by truncating the table before the process begins so the delete doesn't add so many transactions to the log. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[boligoma]

Yes, it was the growth factor in the transaction log. At the beginning it was of 10%, when I put in 50% and it runned without a problem. For the last processes transaction log is growing up to 281 MB.

I'm putting also the instruction for shrinking the database at the beginning and at the end of the processes so that it won't reach the limit of the disk.

thanks for the answer, I wanted to put more stars into de post but it only let me put one.

Ismael Martínez L.
Intellego S.C.