November 14, 2019 at 4:37 am
Index maintenance and loading data conflict with each other cause the log file to grow and disk space ran out of space. Maintenance job failed cause log file full and impacted the load to fail. So would it be possible that index maintenance is rebuilding the same index where the load is happening? I would think in order not to happen this again either i am thinking run the loading process with smaller transactions and run the maintenance job in different time so that they don't conflict with each other. Would you think of anything else?
Thanks in Advance!
November 14, 2019 at 9:11 am
Make sure you run your index maintenance at a time when nothing else is likely to be going on. Ensure you are taking frequent backups of your transaction log. Consider switching to BULK LOGGED recovery mode during your load if the process performs any operations that could benefit from that.
John
November 14, 2019 at 5:08 pm
Thanks John. During the bulk logged recovery mode since it's not fully logged operations, would that means i would be ability to lose point in time restore right?
November 14, 2019 at 6:12 pm
I’m not sure if we have a no activity window. Users will be accessing and saving documents at any time to our system. Seems like huge loading process is a one time deal. However, there could be backfills of similar or greater volume, so this could end up occurring again. This is vendor application to load files into database. I’ve been loading one file in at a time. I am not seeing the log growth this time may be changing the frequency to run log backups more often than before. How can i check is it releasing the transaction after each of the file loads? Is that possible? Do you think it should be done during window of downtime?
November 15, 2019 at 9:02 am
Yes, point-in-time recovery isn't possible from a log backup that includes a bulk-logged operation, but, crucially, you don't have to do a full backup when you switch back to FULL, as you would if you had switched to SIMPLE instead of BULK_LOGGED. Confused? This may explain it better.
If you don't have any quiet times, then at least make sure your loads and index maintenance are scheduled at different times, and avoid the very busiest times. You can use the log_reuse_wait_desc column in sys.databases to find out whether anything is preventing log space from being reused. Check this after log backups, not after data loads.
John
November 19, 2019 at 1:15 am
My understanding is that while running Index Rebuild or Integrity checkdb would not make database unavailable and it will not affect users other than the performance issues. Any thoughts?
November 19, 2019 at 5:00 am
Index rebuilds won't make a database go offline unless the logfile explodes large enough to fill the disk it's on.
Index rebuilds will make the table data unavailable (which will obviously affect performance1) if you don't do them in an ONLINE fashion but there are some pretty cool workarounds for that (and you should look for workarounds because ONLINE rebuilds suck as almost as bad as REORGANIZE).
IMHO, never use ONLINE or REORGANIZE unless someone holds a gun to your head or you've one something really stupid with the LOB datatypes. If you've got VARCHAR(MAX) in your tables an you've not forced the large datatypes to go out of row, you've been a bit ignorant (I know I was) but REORGANIZE isn't going to be your savior even then.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply