Log Files growing beyond belief

[philosinger]

I have several databases that we utilize to create and manipulate very large databases. I am getting in to situations on some of these where my log files are growing larger then my data files. This is a problem because when I do a Shrink Database on the database the data file shrinks but the log file does not.

What can I do to limit the size of my log files?

I have attempted to set a fixed size for the log files and now allow them to expand, however, this does not work because sometimes a query will crash with an error that the log file was full and the update could not complete. I have the Truncate log on checkpoint option turned on all databases.

Any ideas?

 

[Cagliostro]

Just detach databases, remove the log file and attach the database again. John Fill

ivfmd@mail.md

 

[dbmsguy]

depending on what you are doing, you can do iterative updates/deletes or whatever in a loop

loop begin.....

set rowcount 10000
your manipulation
check how many rows are updated. if zero get out
loop end...

basically, in some fashion you need to break up your transaction in to smaller chunks... the above will work, but depending on how you check the loop, you may have performance problems... obviously, you need something to get you out of the loop. Paul

http://www.dbmsguy.com

 

[foxdev]

See the FAQ in this forum:
faq183-345
Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com