DML operation with out logging - SQL Server

Question

Is there any way that we can do DML operation with out logging it into log file?

For example, when I am inserting a row in database, this operation will be logged in log file irrespective of using transaction or not. But I don't want to log this operation in the log file.

Answer 1

No. Never. Not possible.

Every operation is logged for a reason: what if it fails halfways through? Server crashes? etc etc

In summary: the A, C and D in ACID

If you don't want this, then using I would really consider using non-ACID NoSQL alternatives.

Answer 2

SQL Server will always write an insert to the log file. It needs that information to recover a database in a consistent state when the server resets.

You can change to impact logging has by choosing the recovery model:

alter database YourDb set recovery { FULL | BULK_LOGGED | SIMPLE } 

In simple mode, SQL Server logs just running transactions, and starts reusing parts of the log that are no longer needed. In simple mode, the log file typically remains small.

If you want to get rid of logs after an insert operation, you can use:

alter database YourDb set recovery simple with no_wait
dbcc shrinkfile(YourDbLog, 1)
alter database YourDb set recovery <<old model here>>

Note that this gets rid of all logs. You can only use the restore the last full backup after running this command.