June 19, 2013 at 8:35 am
Dear all
If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.
(I do not take transaction log back up at all.)
Will these recovery models give same results or am I missing some thing.
Note that I donot want to restore to particular point of time. Only want to restore to last back up.
Lots of info availsble on net but for my situation I feel recovery model does not matter. Correct me if i am wrong.
Also want to check if for recovery model full if i take only full back up every day will the transaction log keep on growing or it will truncate when full bavkup taken?
Thanks and regsrds
June 19, 2013 at 8:39 am
Hi,
If your recovery model is full and you don't take a log backup then your log file will fill the drive it is sat on. If you don't care about point in time recovery use the simple recovery model.
Thanks,
Simon
June 19, 2013 at 8:50 am
Thanks for quick reply. How do I test this. I need to show the diff that log will not get trucated even if we take full back every day. Can u tell so.e was to demonstrate the difference between a imple and full recovery model
June 19, 2013 at 8:58 am
Krishna1 (6/19/2013)
Thanks for quick reply. How do I test this. I need to show the diff that log will not get trucated even if we take full back every day. Can u tell so.e was to demonstrate the difference between a imple and full recovery model
It is easy to test this
Follow the below steps:
1. Set the recovery model as FULL
2. Create a table in the database
3. Insert lots of data in the table and check the log size
4. Take a FULL backup
5. Repeat steps 3 & 4 as many times as you want
Note down the log size after every iteration and you will find that the log size keeps on increasing.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 19, 2013 at 8:59 am
Krishna1 (6/19/2013)
How do I test this. I need to show the diff that log will not get trucated even if we take full back every day.
Create a database, set it to full recovery. Schedule full backups. Run data modifications (create, populate and drop a table if you have nothing else). Wait for the full drive error.
Please read through this: http://www.sqlservercentral.com/articles/Administration/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 10:07 am
If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.
(I do not take transaction log back up at all.)
Will these recovery models give same results or am I missing some thing.
---- Taking Full Backup on the database which is in either Full or Simple or Bulk-logged recovery model will give the same result only and there won't be any difference.
Thanks
Bala
June 19, 2013 at 1:47 pm
balasaukri (6/19/2013)
If db recovery model is simple and I take full back up every day. And if my recovery mdel is full and I take only fulll backup every day.(I do not take transaction log back up at all.)
Will these recovery models give same results or am I missing some thing.
---- Taking Full Backup on the database which is in either Full or Simple or Bulk-logged recovery model will give the same result only and there won't be any difference.
Thanks
Bala
Sorry, but that's wrong. The recovery model setting has no influence on when and whether the log files get "cleared". Just follow Gail's link a little further up for an explanation as to why.
June 19, 2013 at 9:30 pm
Aplogies if I didn't mean what I actually mean.
I mean to say the content (data) of backup file will be same. If a full backup is taken on a database with any type recovery model - the content (data) in the backup file would be the same.
And ofcourse Full backup will make certain effect on the Logfile - It won't mark log file for deletion (truncation) if a db in Full or Bulk Recovery model. It will take certain portion of Log file for sychronization.
Only Log backup will mark the log file for deletion (truncation) after the log backup except two situations - (i) When it is being using used with conjuction of NO_TRUNCATE or COPY_ONLY. (ii) When the log backup completes while Full/ Differential backup is still running. As the Full/ Differential backup requires certain portion of log backup it wont allow log backup to truncate.
June 19, 2013 at 11:36 pm
Dear All
Thanks for your replies. I have done testing of .ldf file size using 2 newly created DB. One DB is simple recovery model and other Full recovery model.
Created following table in both DBs
create table a ( a1 char(1000) default 'a1',
a2 char(1000) default 'a2',
a3 char(1000) default 'a3',
a4 char(1000) default 'a4',
a5 char(1000) default 'a5',
a6 char(1000) default 'a6',
a7 char(1000) default 'a7',
iinteger)
and used
insert into a (i) values (1)
go 20000
Please refer to the pdf file of my findings. From the PDF file i noticed that Full recovery model after transaction log back up us taken it resused the log space. But in the Simple recovery model its keeps on growing, which i know is not correct.
Let me know what is that i am doing wrong
Regards
Krishna1
June 19, 2013 at 11:52 pm
Please post the output
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name = 'your db name'
dbcc sqlperf(logspace) [for the two databases]
June 20, 2013 at 12:17 am
pls find the screen shot in the attachment
June 20, 2013 at 2:01 am
First Screen shot is not clear - another image is on that. Moreover the log_reuse_wait_desc for t1 (if it is SIMPLE) looks as LOG_Backup... It can't be. How you created this db (t1) really don't know. Please create another db with SIMPLE and repeat the tasks as you mentioned. It should work. No way for the misbehaviour.
June 20, 2013 at 2:16 am
sorry did not realise. I have corrected the screen shot in this attachment.
I created t1 using ssms. and it shows the recovery model as simple
June 20, 2013 at 2:51 am
(i) Displaying Log_reuse_wait_desc as Log_Backup for a db in SIMPLE contradicts.
(ii) Is there any active transaction pending on this db?
(iii) Change the Recovery to Full.... and then again to SIMPLE then try.
(vi) Have you tried to create a new db in SIMPLE and do the same exercise?
June 20, 2013 at 3:57 am
That means you just switched to simple and a checkpoint hasn't occurred. The wait types don't change instantly, run a checkpoint and the Log Backup will disappear.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply