Log filling, too many locks

Log filling, too many locks

Post by Bob Teijem » Thu, 26 Mar 1998 04:00:00



I have this problem with my log filling and running out of locks. I
think the reason for this is in the wrong placement of the BEGIN TRAN
and END TRAN statements. There are some nested transactions. Could this
be the reason for the problem?
If so what should I do about it?
If not, what could be causing the problem?
For your information: the log device is 500MB, the maximum number of
locks is 50,000. When looking in the current activity window during the
running of the stored procedure the window is filled with 'commit tran'
statements. (really hundreds to thousands of them)
Any help would really be appreciated.
Thanking in anticipation,
Bob Teijema

 
 
 

Log filling, too many locks

Post by Steve Robinso » Fri, 27 Mar 1998 04:00:00


Bob,

The transaction statements will have an effect on the database as they will
hold all the locks until the transcation is committed.  Basicaly here are a
few ideas but unless I know exactly what it is you are trying to do ther is
no real way of telling what is wrong.

Do you have truncate log on checkpoint on for that database
To check out the number of locks you are using try running select count(*)
from syslocks

To cut this down maybe what you can do is run your data manipulation in
batches of 1000 eg to update a table X with field Y from 1 to 2

set rowcount 1000
while exists (select * from X where Y =1)
begin
    update X set Y = 2 where Y = 1
    dump transaction <database name> with no_log
    dbcc checktable (syslogs)
end
set rowcount 0
go

I hope this was of use to you.

Steve Robinson


>I have this problem with my log filling and running out of locks. I
>think the reason for this is in the wrong placement of the BEGIN TRAN
>and END TRAN statements. There are some nested transactions. Could this
>be the reason for the problem?
>If so what should I do about it?
>If not, what could be causing the problem?
>For your information: the log device is 500MB, the maximum number of
>locks is 50,000. When looking in the current activity window during the
>running of the stored procedure the window is filled with 'commit tran'
>statements. (really hundreds to thousands of them)
>Any help would really be appreciated.
>Thanking in anticipation,
>Bob Teijema


 
 
 

1. Why does 5.04 lock up when logs fill?

I guess the subject says it all. the server just quits and you have to
restore the database? This is what our admin people are doing, anyway
and its a 6 hour process and all you get is what you had on your last
backup. Doesnt seem like the last word in data safety/reliability.

somebody does a transaction which affects quite a few rows of the
database, the logs fill, and everything comes to a very quiet halt.

tom.

2. Manipulating field of RAW datatype

3. transaction log fills every 30 days log file size is 500MB is this normal

4. Remove replication from a restore

5. Exclusive database lock (no logging / logging)

6. What is two-phase commit?

7. Change from Buffered log to no log locks databases

8. odbc vs proc/c++ ??

9. Fill Factors/ Locking

10. Merge filling log

11. Event viewer application log filling up with SQL logins

12. Transaction Log fills up too fast on ALTER COLUMN script

13. Transaction Log Filling Up -