Skip to content

Is truncate logged or non-logged in SQL Server?

by on August 29, 2011

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

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: