A small Twist in Full Recovery Model Of SQL Server...

Hi Friends!! In this small blog post I'm going to tell you a very interesting thing about Full Recovery model. I know most of peoples are aware about it but in case if you don't then I'm sure you will not believe me about this .  :) :)

As we all know we have three types of recovery model in SQL server which we use according to business requirement:

Simple Recovery Model
Bulked logged Recovery model
Full Recovery Model

For more information around recovery model in SQL Server you can visit Below Page:
http://msdn.microsoft.com/en-IN/library/ms189275.aspx

In this blog Post I'm going to tell a secret about full Recovery model. 

So, Let's assume that you a database "TestVK" in simple recovery mode which is running from last one month. Today morning when you reached to office, your manager came to you and asked to changed the recovery model from simple to Full Recovery . You agreed and and then from database options you changed it to Full Recovery model. You confirmed your manager that job is done.

Now my questions is: Will the database "TestVK" work as Full Recovery model From Now Onwards??

If your answer is "NO" then close this tab and ENJOY!!! :)

If your answer is "YESSSS.. It will work as Full Recovery Model" then please wait because I think this blog is for you :)

I know you will say that this blogger crazy??.. As you just confirmed it from sys.database and itis giving the recovery model as "Full" then how can I say it is not working as Full Recovery Model.

select name,recovery_model_desc from sys.databases where name='testvk'

But let me tell you the fact that " Full recovery model does not work as "FULL" until unless you take a Full Backup of your database". As we use Full recovery model to prevent truncation of transaction log of your database. It can only be truncated by a transaction log Backup of that database except few cases. And during PIT (Point in Time) restore you need a base that is your Full Backup. And if you don't have any full backup then what is the use of transaction log. That is why when we don't take any full backup, Sql server work as Simple recovery model. As Simple recovery model Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. 

Let me show you same in SSMS. Lets crate a database "TestVK". It was initially in simple so I have changed it to Full Recovery Model. Go to options and disable auto log growth. Now create a Table and Insert the data in table to generate logs as it is in Full recovery model so log should not be truncated. Am I Right?? Okay then let see what happens here. 


Use below script to create environment:

USE MASTER
GO
CREATE DATABASE TESTVK
Go
Alter database TestVK Set Recovery Full
Go
Create table Test1 (Id int Identity(1,1), name char(500))
Go

Lets Verify the recovery model of database using Sys.databases: 

select name,recovery_model_desc from sys.databases where name='TestVK'

Output:
name recovery_model_desc
TestVK FULL

Now Disable auto log growth. 

Check the Log usage of this database using DBCC SQLPERF(Logspace). If you want more information around SQLPERF(Logspace) Please visit my old blog post on same:

How to see the log usage information of databases??

Now Execute: DBCC SQLPERF(LOGSPACE)

Output:
Database Name Log Size (MB) Log Space Used (%) Status

TESTVK                   0.5546875 42.78169                     0

Now lets insert the data into Test1 table and will monitor the log space usage:

insert into Test1 values ('vimal')
go 200

Execute
DBCC SQLPERF(LOGSPACE)

Output:

Database Name Log Size (MB) Log Space Used (%) Status

TESTVK                        0.5546875 80.72183                     0

Again Insert:

insert into Test1 values ('vimal')
go 200

Execute
DBCC SQLPERF(LOGSPACE)

Output:
Database Name Log Size (MB) Log Space Used (%)                Status

TESTVK                   0.5546875 41.10915 (Reduced)                    0


From above log usage output we can see that Log is getting managed in 1MB of File.
It means that although it is in Full Recovery Model but internally it is working as Simple Recovery Model.

Now lets take the Full back of database and then we will monitor the log space usage:

BACKUP DATABASE [TestVK] TO  DISK = N'D:\TestVK_backup_2014_12_23_220007_1630000.bak' 
WITH stats=10
GO

OUTPUT
12 percent processed.
21 percent processed.
31 percent processed.
40 percent processed.
52 percent processed.
62 percent processed.
71 percent processed.
80 percent processed.
90 percent processed.
Processed 256 pages for database 'TESTVK', file 'TESTVK' on file 2.
100 percent processed.
Processed 1 pages for database 'TESTVK', file 'TESTVK_log' on file 2.

BACKUP DATABASE successfully processed 257 pages in 0.216 seconds (9.295 MB/sec).

Now insert
insert into Test1 values ('vimal')
go 200

Execute
DBCC SQLPERF(LOGSPACE)

Output:

Database Name Log Size (MB) Log Space Used (%) Status

TESTVK                     0.5546875 78.34507                  0

Again insert Data in table:
insert into Test1 values ('vimal')
go 2000

Error 
** An error was encountered during execution of batch. Continuing.
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'TestVK' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Execute
DBCC SQLPERF(LOGSPACE)

Output:
Database Name Log Size (MB) Log Space Used (%) Status

TESTVK                            0.5546875         100                      0

Ooooops Log FULL :) :)

So now its clear that it is working as full Recovery model. I hope you got my point what I want to explain here.

So, Next time when you are changing recovery model from simple to Full please do not forget to take a full backup.

Thanks For reading this Blog!!!