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.