June 10, 2013 at 3:15 pm
we setup a purge process and had about 20GB of free log space but kept filling up the T-log.
After breaking into smaller chunks I realized that the amount of log space required far exceeded the total db size.
On one particular table (see definition below) the delete command loaded 15GB into the T-log. This was only for a fraction of the data within the 19GB database.
I'm just looking for an explaination as to why this is. This table does have 3 Text type columns, my thought is that this data is compressed within the table but cant be within the log. Any ideas on this would be appreciated. Thanks.
[p]
Data TypeLengthscaleprecision
int4010
datetime8323
char100
char400
char800
char600
varchar4700
varchar4000
tinyint103
tinyint103
char300
char2000
char800
char800
char800
char1000
char700
tinyint103
tinyint103
tinyint103
char300
char600
tinyint103
tinyint103
char400
tinyint103
int4010
smallint205
char300
char400
char600
tinyint103
text1600
text1600
text1600
datetime8323
datetime8323
char1500
char1500
char300
[/p]
June 10, 2013 at 3:56 pm
Are you doing a row at a time delete, or deleting multiple rows at a time?
Do any of the tables you are deleting from have a delete trigger that is performing additional inserts, updates, or deletes?
Are any of the tables you are deleting from referenced by foreign key constraints that have cascade delete enabled?
June 10, 2013 at 4:45 pm
Doing set based deletes, no triggers, and no dependancies of any kind for that table.
June 10, 2013 at 4:56 pm
Looking at this:
text 16 0 0
text 16 0 0
text 16 0 0
You do realize that the 16 bytes is simply the pointer to the data. What about the data itself?
June 11, 2013 at 6:22 am
Also log space reservation means that the operation may need quite a bit more than double the data size to be available in log capacity.
I've seen up to 5x data size needed in log space (not deletes though). Apparently that's how it is supposed to work, the reservation algorithm is very conservative.
It has to be conservative, because if a rollback fails, the db is automatically thrown offline, in an incosistent state:crazy:
Cheers,
JohnA
MCM: SQL2008
June 11, 2013 at 6:29 am
If there are no dependencies on the table and you're deleting a large portion of it, you'll find it a lot less expensive to create a new table with the same structure, insert the rows you want to keep into that, switch the table names around, then drop the old table.
June 11, 2013 at 8:14 am
Lynn Pettis (6/10/2013)
Looking at this:text 16 0 0
text 16 0 0
text 16 0 0
You do realize that the 16 bytes is simply the pointer to the data. What about the data itself?
Hey Lynn,
I do understand that its a pointer but the data is still stored within the MDB file and would show as part of the file size. My thought was that the data is somehow compressed when stored within the MDB but when written to the tlog it consumes much more space.
Also understand this is not an issue, it's something I want to understand better. Probably not worth wasting much time since the TEXT/NTEXT will be gone soon anyway. This is a one time deal once we get this purge done its going to run daily so this wont be an issue.
June 11, 2013 at 8:38 am
Are you using compression at the row or page level in your database? If you are then the data would be compressed in the tables and/or indexes but I do not believe that the data is compressed in the transaction log. this would explain the difference between the MDF and LDF.
June 11, 2013 at 10:00 am
LOB data (TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) is only compressed if it is stored in-row.
June 12, 2013 at 11:02 am
Deleting rows in chunks is the way to go, but you also need to perform a checkpoint and transaction log backup between each iteration. That will insure that the inactive portion of transaction log is truncated, thus allowing the space to be reused rather than growing the file.
Index pages are logged too, so dropping indexes will reduce the amount of transaction logging by the delete. After performing a mass delete, the indexes will be fragmented anyhow, so you just as well drop them and then re-create them fresh after the delete process is over. Remember to drop clustered index last, and recreate clustered index first.
Also, it will help if you insure that there are no other processes creating transactions until you complete this, because other active transactions can prevent truncation. If it were me, I'd do this off hours, disable jobs, and place the database in restricted_user mode. Start the delete process, backing up transaction log between each loop. When it completes, then re-build the indexes and put database back in multi_user mode.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 12, 2013 at 1:32 pm
Eric M Russell (6/12/2013)
Deleting rows in chunks is the way to go, but you also need to perform a checkpoint and transaction log backup between each iteration. That will insure that the inactive portion of transaction log is truncated, thus allowing the space to be reused rather than growing the file.Index pages are logged too, so dropping indexes will reduce the amount of transaction logging by the delete. After performing a mass delete, the indexes will be fragmented anyhow, so you just as well drop them and then re-create them fresh after the delete process is over. Remember to drop clustered index last, and recreate clustered index first.
Also, it will help if you insure that there are no other processes creating transactions until you complete this, because other active transactions can prevent truncation. If it were me, I'd do this off hours, disable jobs, and place the database in restricted_user mode. Start the delete process, backing up transaction log between each loop. When it completes, then re-build the indexes.
+1
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply