SQL Server Recovery Model Deep Dives

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

if object_id('dbo.m33n@l','U') is not null drop table m33n@l
	go
	 drop table #temp2
	go
	checkpoint
	go
	alter database akash set recovery full
	go
	select * into akash..m33n@l from AdventureWorks2012.Production.TransactionHistory
	go

	select * into #temp2 from fn_dblog(null,null)
	go

	if object_id('dbo.m33n@l','U') is not null drop table m33n@l
	go
	 drop table #temp3
	go
	checkpoint
	go
	alter database akash set recovery simple
	go
	select * into akash..m33n@l from AdventureWorks2012.Production.TransactionHistory
	go

	select * into #temp3 from fn_dblog(null,null)
	go
	if object_id('dbo.m33n@l','U') is not null drop table m33n@l
	go
	drop table #temp4
	go
	checkpoint
	go
	alter database akash set recovery bulk_logged
	go
	select * into akash..m33n@l from AdventureWorks2012.Production.TransactionHistory
	go

	select * into #temp4 from fn_dblog(null,null)
	go

	SELECT a as Recovery_Model,
	[LCX_GAM],[LCX_HEAP], [LCX_PFS], [LCX_IAM]
	FROM
	(
	select 'Full' as a,[Log Record Length] ,Context  from #temp2 where AllocUnitName ='dbo.m33n@l' 
	union all
	select 'Simple' as a,[Log Record Length] ,Context from #temp3 where AllocUnitName ='dbo.m33n@l'
	union all
	select 'Bulk Logged' as a,[Log Record Length] ,Context from #temp4 where AllocUnitName ='dbo.m33n@l'
	 ) AS SourceTable
	PIVOT
	(
	Sum([Log Record Length])
	FOR Context IN ([LCX_GAM],[LCX_HEAP], [LCX_PFS], [LCX_IAM])
	) AS PivotTable;

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
Regards

Akash Gautam

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

Follow me on Twitter |  Follow me on FaceBook