Okay, second blog post in one day – slowly trying to recoverJ.
Below is an excerpt from a stored procedure from BizTalk server:
–If you are doing this we assume it is because you have a huge, old db so it is just faster for us to
–find things which are not done and hold onto them then the other way around.
–if we crashed after grabbing these uncompleted instances but before reinserting them, we are okay
–because we have a unique constraint on the serviceinstanceid identity column so we will just ignore dupes
–Note that if you run this, you need to have the tracking host stopped so that no new data is being inserted while this runs.
–This hard purge assumes that inserts have stopped so that we can do unlogged operations like truncate table and not hold locks for
–long periods of time. Make sure if the system crashes while running this, you get a full clean run before restarting the tracking service or
–you will end up in a non-deterministic state.
What struck me was: “This hard purge assumes that inserts have stopped so that we can do unlogged operations like truncate table”. I beg your pardon?
READ THIS: TRUNCATE TABLE IS LOGGED!!! Okay, to do some justice: it’s minimally logged, which in plain English would roughly translate into the fact that only allocation and de-allocation of pages (that is, data file pages) are logged. But this is another discussion (along with ghost cleanup), so we’ll tackle it some other time.
Moral of the story: because somebody from Microsoft said so, it doesn’t mean it is soJ.
Best regards,
io
SQL-PLAY 