brainy (2/21/2011)
What is the difference between minimally logged and fully logged. As per my knowledge, only the difference is, in minimally logged the the log will be reused after committing the running transaction. Where as in fully logged the it is not the case. It will be reused only after taking full backup.(This is the main difference).
Completely wrong.
Fully logged and minimally logged operations have nothing at all to do with when the log space is reused.
Certain operations (and check books online for details) can be minimally logged. This means that instead of SQL logging the complete details of the change (eg in a fully logged insert it will write the values of columns into the transaction log), SQL just logs the details of what pages it changed. This can massively reduce the amount of transaction log used.
Minimally logged operations are (with a couple of exceptions) only possible in bulk-logged or simple recovery models. In full recovery, SQL logs the changes fully. Even in those recovery models, only some operations can be minimally logged (bcp, bulk insert, index rebuilds) and only under certain circumstances
The reuse of the log has to do with recovery model, not the type of operation performed. In simple recovery only the log is reused after a checkpoint, in all other recovery models a log backup is required. See today's headline article for more on transaction log reuse.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability