Why The Enormous Log File From a Delete?
(SQL Server 2008)
I have a database about 12GB in size. It is composed of a number tables holding data organized into "scenarios" - basically a single small master table with several dozen detail tables. A scenario is typically about 2GB of data (that's the size the database increases by adding one).
I recently added relationships from the master table to the many detail tables with cascade on update/delete, because of the need to delete a scenario.
To test, I renamed one by changing the name in the master table. After maybe 40 minutes or so the transaction log file, which started at about 1MB, was 14GB in size and I aborted the operation. I am using simple logging.
I first thought perhaps this was because the PKs on these tables are clustered and the field being updated on these child tables is part of the key - so I thought probably the entirety of the tables was being re-created or something - and some of them are huge. So... redefined most as non-clustered, but the result is the same.
I don't understand how changing this value causes the log to expand to larger than the entire database (and with no end in sight), when the data itself is maybe 1/4th the database.
I don't want to get into the issue of why the tables or keys are designed this way because this is a database designed by others and which I have to work with - I can't at this time do major redesign of the database.
What's going on here? Any ideas?
I have a database about 12GB in size. It is composed of a number tables holding data organized into "scenarios" - basically a single small master table with several dozen detail tables. A scenario is typically about 2GB of data (that's the size the database increases by adding one).
I recently added relationships from the master table to the many detail tables with cascade on update/delete, because of the need to delete a scenario.
To test, I renamed one by changing the name in the master table. After maybe 40 minutes or so the transaction log file, which started at about 1MB, was 14GB in size and I aborted the operation. I am using simple logging.
I first thought perhaps this was because the PKs on these tables are clustered and the field being updated on these child tables is part of the key - so I thought probably the entirety of the tables was being re-created or something - and some of them are huge. So... redefined most as non-clustered, but the result is the same.
I don't understand how changing this value causes the log to expand to larger than the entire database (and with no end in sight), when the data itself is maybe 1/4th the database.
I don't want to get into the issue of why the tables or keys are designed this way because this is a database designed by others and which I have to work with - I can't at this time do major redesign of the database.
What's going on here? Any ideas?
Ved Prakash Agrawal
because your table are huge and you trying to make changes it. for that if will creat a temp table to store your data and drop table and then import back means if you make any change in the table it will drop and create using temp table.
just check the recovery model of your database. if it is in full or bulk recovery mode change to simple.
What you want to mean with: " I am using simple logging."?
Log files have all changes you make in your database, so if you are working with millions of rows it's normal that the log file grows and even pass the data file size.
Log files have all changes you make in your database, so if you are working with millions of rows it's normal that the log file grows and even pass the data file size.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WMontalvao: "Simple logging" - meant simple recovery model. One of the types of logging you can specify for a database.
acperkins: Doesn't begin to address the question - how can my changing probably 1.5GB of data in a 12GB database result in a 14GB log file by the time I gave up? Even if every row of every table were in the log file it shouldn't be that large, but nothing like that should be logged. I am not asking for a solution to what I should do INSTEAD of just deleting the related rows - of course I can break it down into chunks. I would like to understand why the log file grows to almost 10 times the size of the data being altered (assuming the full row of data even, rather than just the field being altered).
acperkins: Doesn't begin to address the question - how can my changing probably 1.5GB of data in a 12GB database result in a 14GB log file by the time I gave up? Even if every row of every table were in the log file it shouldn't be that large, but nothing like that should be logged. I am not asking for a solution to what I should do INSTEAD of just deleting the related rows - of course I can break it down into chunks. I would like to understand why the log file grows to almost 10 times the size of the data being altered (assuming the full row of data even, rather than just the field being altered).
Ok rberman. I just wanted to check if we was talking the same language :)
Even that can seems impossible it isn't. Your log file can really grows that much. All depends about want kind of operation are you doing and how often.
Even that can seems impossible it isn't. Your log file can really grows that much. All depends about want kind of operation are you doing and how often.
ASKER
VMontalvao: I stated the operation: It is a delete operation - a single delete transaction. But here's more info - the delete covers about 1 to 2 GB of data spread out over probably 2 million rows, which are distributed in about 70 tables. The operation grows the log by AT LEAST 14GB - at which time I've canceled the operation. I don't know how large the log might eventually get if I let it run. I may try that on a larger system than my development system.
>>but nothing like that should be logged. <<
Despite popular myth, even in Simple Recovery Model there is logging involved.
>>I don't know how large the log might eventually get if I let it run. <<
And you will never know if you keep interrupting it.
Good luck.
Despite popular myth, even in Simple Recovery Model there is logging involved.
>>I don't know how large the log might eventually get if I let it run. <<
And you will never know if you keep interrupting it.
Good luck.
ASKER
acperkins: I fully expect logging - I am saying that 14gb (and still growing) of logging to update at MOST 2GB of data - and in reality a lot less - is unreasonable.
As for interrupting it - maybe if my disk automatically grew more GB it would keep running. Afraid there was no choice in that configuration. I kinda thought 15GB of free space - which is all there is on that system- was enough.
It really doesn't matter how large it might get - the operation is unreasonable in time and disk space. If you have some idea as to what might be done about it, THAT would be most welcome.
As for interrupting it - maybe if my disk automatically grew more GB it would keep running. Afraid there was no choice in that configuration. I kinda thought 15GB of free space - which is all there is on that system- was enough.
It really doesn't matter how large it might get - the operation is unreasonable in time and disk space. If you have some idea as to what might be done about it, THAT would be most welcome.
>>If you have some idea as to what might be done about it, THAT would be most welcome<<
I tried. Your response: "Doesn't begin to address the question"
Now I need to get on and help people that actually need my help.
Good luck again.
I tried. Your response: "Doesn't begin to address the question"
Now I need to get on and help people that actually need my help.
Good luck again.
ASKER
Sorry you feel that way. Not sure how putting down (incorrectly) my understanding of logging, or wondering how much further overlarge the log file would get was helping. Yes, please move on.
rberman, you have delete triggers in those 70 tables?
ASKER
VM: Good question. No - no triggers of any kind on any of them - this should be just a simple cascade delete.
ASKER
Nope - acperkins did not answer the actual question, but some other question I never asked. I say it is simply abandoned, without answer.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
with due respect, but acperkins did both:
* explain that the log file IS used, even in simple recovery mode, and that you must let the statement complete
* unless you break the statement into batches to delete, for example, 1000 rows at once, which is the solution to have not such big .ldf file as the result of your delete ...
but I will leave the decision to the moderator that has been called by your objection.
a3
* explain that the log file IS used, even in simple recovery mode, and that you must let the statement complete
* unless you break the statement into batches to delete, for example, 1000 rows at once, which is the solution to have not such big .ldf file as the result of your delete ...
but I will leave the decision to the moderator that has been called by your objection.
a3
ASKER
Afraid not - the question was why does deleting about 1.5GB of data result in logs many times larger than the deleted data. I did not ask whether logs were used, or why.
the reason is that for the data that is deleted, the log has some overhead to be stored.
and as you deleted not from a single table, but from multiple (via the foreign key cascaded), the overhead is significant.
the "exact" calculations are not known to me, you would need to contact MS about the "formulas" ...
and as you deleted not from a single table, but from multiple (via the foreign key cascaded), the overhead is significant.
the "exact" calculations are not known to me, you would need to contact MS about the "formulas" ...
ASKER
Leaving this to the moderator now