Database Technologies‎ > ‎MSSQL‎ > ‎

Transactional Log

Any changes required to be made to the database are written
to the transaction log, for example any insert,update,delete done
on the database are logged first in the transaction log file. When
transaction is committed (explicit/implicit) the data is marked to be
written to the database.

Two types of transaction

Explicit  = You specify where to start the tran and when to end it
Implicit  = We dont specify begin and end tran

From Recovery Models point of view


1> Full Recovery : Every transaction made to the database
                           is first logged to the log file

2> Bulk Logged : When there is bulk operation is minimally logged

3> Simple : Completed transaction log are deleted from log file

SELECT name,cmptlevel,DATABASEPROPERTYEX(name,'Recovery')AS RecoveryModel, 
DATABASEPROPERTYEX(name,'Status') as Status FROM sysdatabases.

Above will give the list of database and the configured recover model of each.