| Author |
Topic |
|
thierryVD
Starting Member
11 Posts |
 Posted - 2007-06-28 : 10:57:43
|
| HiI was wondering how it is possible for a transaction log to grow to 9Gb when only deleting approx. 600Mb of data (6 million rows at about 100 bytes)? ThxThierry |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-28 : 11:29:40
|
| Cascade delete perhaps?? |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-28 : 23:37:51
|
| How long the process last? Any index involved? Any other processes during that time period? |
|
|
|
thierryVD
Starting Member
11 Posts |
Posted - 2007-06-29 : 07:21:04
|
| Deleting takes about 2 hours (which is not a problem for me, unless you think this is too long)The table doesn't have any relations so cascading is not an issue. It has a couple of indexes though.I'm trying to find out if there is generic way to predict the transaction log growth when deleting records. I was thinking: N rows * X bytes + (size of the indexes). I don't know how to calculate the exact (size of the indexes) because I haven't read the chapter on indexing internals in my book yet :), but I use an estimate. Anyway, even including the index stuff in the calculation I'm sure 9.5Gb is still an awfull lot more than my simple calculation.As far as I know at the time of the deletion no other processes are running (these only run during the night).I'm a very curious person so I'd really like to know more on thisAlso, is there a way to display the contents of a transaction log, more specific which portion is active and which not? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-29 : 09:06:17
|
| "I'm a very curious person so I'd really like to know more on this"If I was you I would want to know too!"Also, is there a way to display the contents of a transaction log, more specific which portion is active and which not?"There are 3rd party tools that can read the log, they may be able to tell you what is in it, and thus why it is so large.If the data tables were very sparse when you made your delete is it possible that what was actually logged was data pages which happened to have very little deleted data on them? I don;t know how logging works, whether its the Record or the Data page that is logged.Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-29 : 09:07:28
|
| Deleting a small portion of the records in a loop with a short WAITFOR and running TLOg backups frequently would reduce the growth of the log (but not you total backup file size of course!!)Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-30 : 21:31:39
|
| If there were any other db update processes during that time period, sql is unable to truncate those transactions even they were committed. |
|
|
|
thierryVD
Starting Member
11 Posts |
Posted - 2007-07-02 : 02:50:05
|
| Hi again,to be sure of no other activity I'm going to put the db in single user mode and see what happens then. Fyi, after deletion the log usage drops back to a few Mb.I'm also going to try and find some more info on what is stored in the transaction log when you delete a row. I'll be back with my findings.Thx for sticking up with me so far (being a beginner and all) :) |
|
|
|
thierryVD
Starting Member
11 Posts |
Posted - 2007-07-02 : 05:23:26
|
| I put the database in single-user mode and deleted 8839575 rows. It took 2h 22min and the log grew to 12Gb...After deletion, the log usage was 61Mb (physical file was still 12Gb)I will be running out of records soon so before I do any further testing I guess I will first try to find more info on the deletion process with regards to the transaction log growth, unless someone out there already has an answer... |
|
|
|
Kristen
Test
22859 Posts |
|
|
thierryVD
Starting Member
11 Posts |
Posted - 2007-07-03 : 06:19:30
|
| Hey KristenI'm sure that's a good solution. Anyway, I was merely posting just to get some understanding about the log growth. I don't run into any problems (yet).I tried the following as a test: create a job with 3 steps, each step deleting about 2 million rows. The result was the same: the log kept growing as if I was just deleting the rows at once. Seems strange unless 1 job with multiple steps is considered as on transaction (but I don't think so). Using perfmon I did see an occasional drop in log size usage around each step start but not substantially. It probably does indicate that the job is just 3 seperate delete transactionsAnyway, I will keep searching for more answers... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-03 : 07:18:14
|
| If your database is Full Recovery Mode then you need a Transaction log backup before the log will be reused.If its Simple Recovery Mode you need a checkpoint - which probably happens on a timer [I think], so maybe your three batches were too quick?Could also be that there are other (incomplete) transactions which prevent truncation of the log file.Kristen |
|
|
|
thierryVD
Starting Member
11 Posts |
Posted - 2007-07-04 : 04:52:03
|
| Yep, probably checkpointing was slow. I did some tests again with 3 different steps and there was a definite "reuse" of the log space |
|
|
|
|
Bulk delete and the transaction log