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.