Hello friends

One of the Fav question asked in most of the interviews is to explain diff SQL Server recovery model .

All they want to listen from you is word “Minimally logged”.

Actually there are two types of Logging physical logging and logical logging ,most of the database systems use physiological logging.Logical logging involves logging logical operation like ‘insert into akash values(blah,blah)’ while physical logging involves the before and after images for all byte ranges modified via the tuple insertion, including bytes on both heap file and index blocks ,when database is in simple recovery mode or in  bulk logged recovery mode only minimal operations are logically logged and in full recovery mode all operations are Physically logged.

Not going into much of theory here is my demo which compares  size of logging based on different oprations involved in different recovery mode

For this demo I have created a database akash and inserted data into a table m33n@l from AdventureWorks2012.Production.TransactionHistory and i have used fn_dblog for further analysis,checkpoint command is necesoory here to flush dirty data


Note here for All recovery modes LCX_GAM,LCX_PFS and LCX_IAM are getting logged by equal amount so they are doing physical logging on IAM PFS and GAM page but if you compare the logging size of Heap they are drastically changing between full to simple or bulk logged.Hope this blog will clear some of your concepts of recovery

Akash Gautam

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook