Systems Engineering and RDBMS

Minimally logged and the recovery model

Posted by decipherinfosys on November 9, 2009

Yesterday, while attending a conference call related to a production issue at a client site, we had to emphasize to the client DBA one of the concepts related to minimally logged operation.  It is well documented, however people tend to miss this information.

In SQL Server, there are several minimally logged operations which essentially means that only enough information  is logged to be able to recover the transaction and point in time recovery is not supported for those minimal logged operations.  Such operations are typically used for bulk data processing.  Key point to remember is that if you are running under the FULL recovery model, ALL the operations are fully logged including the operations that are supposed to do minimal logging.  You need to be in the simple or bulk logged recovery model in order to take advantage of the minimally logged operations.

As far as the inserts go, the minimal logged operations are: BULK INSERT, bcp, SELECT … INTO and in SQL Server 2008 – INSERT … SELECT.   But there are certain pre-requisites for these operations to be minimally logged which you can read more on from the BOL link below.  There are some index DDL operations and also inserts/updates to the text/ntext/image data type columns that can be minimally logged.

You can read more on this topic from BOL – here and here.  And if you are a subscriber of the SQL Server Magazine, there is an excellent article by MVP Itzik Ben Gan on minimally logged inserts – here.

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: