July 1, 2009 at 5:17 pm
Hi Friends,
Small clarification needed on Recovery Models.
As per my knowledge, If we choose Simple recovery model, we cannot take
Transaction Log backup. Right! and the reason for this whenever 70% of
the Log is full , Sql server issues a CKPT and committed txns are written back to .mdf file and that space if freed up and it can be overwritten other txns in the .ldf file. And so for this reason, we dont often see any increase in the Transaction Log. And so for that very reason, i.e. since it is getting overwritten, there is no use of taking the T-Log backup. This is again as per my understanding. pl correct me if am wrong!!!
Coming to Bulk logged recovery model, only minimal things are logged
inside my Transaction - log i.e. not every operation is logged.
and if we do bulk insert operation say (10 million records) am loading,
then it wont log all my 10 million operations in T-log. Right!
Then my question is , why did Microsoft has provided a T-Log backup in
the case of Bulk Logged Recovery Model.
Also, if anything failure occurs in the middle of my BCP operation
entire thing is ROLLBACK'd. Then what is the point in taking the T-log
backup as in case of Bulk logged Recovery Model. How it is going to
help me out during my recovery phase?
can Anyone help me out in understanding??
Any practical scenario will be more helpful!!!
Thank You!
July 1, 2009 at 5:42 pm
mahesh.vsp (7/1/2009)
Hi Friends,Small clarification needed on Recovery Models.
As per my knowledge, If we choose Simple recovery model, we cannot take
Transaction Log backup. Right! and the reason for this whenever 70% of
the Log is full , Sql server issues a CKPT and committed txns are written back to .mdf file and that space if freed up and it can be overwritten other txns in the .ldf file. And so for this reason, we dont often see any increase in the Transaction Log. And so for that very reason, i.e. since it is getting overwritten, there is no use of taking the T-Log backup. This is again as per my understanding. pl correct me if am wrong!!!
Not necessary at all times when 70% of log is full when CKPT is issued transaction recorded in log file are flushed.
Consider a scenario where there is log record for a active transaction(Transaction2) which is not committed and is being still active in VLF2, there is an another big transaction(Transaction1) which has consumed space for its log records in VLF1 ,VLF2 and then extended to VLF3. Now Transaction1 has completed its entire task, but transaction2 is not done yet.
Considering Transactionlog has consumed 70% of its space, in this scenario even if sql server issues check point you cann't reclaim transaction log space as there is an active log record in VLF2, in order to flush log records, all log records
in respectiveVLF should have become inactive, in this case there is an active log record corresponding to Transaction2 even though Transaction1 is done and all log records are inactive, these cann't be flushed as one or more records are still active in the VLF2.
Transaction log file will not release space after it has consumed 70% of allocated space, after reaching 100% full if this problem still persists then log file grows if it is set to autogrowth or else the transaction will fail.
Coming to Bulk logged recovery model, only minimal things are logged
inside my Transaction - log i.e. not every operation is logged.
and if we do bulk insert operation say (10 million records) am loading,
then it wont log all my 10 million operations in T-log. Right!
Then my question is , why did Microsoft has provided a T-Log backup in
the case of Bulk Logged Recovery Model.
Also, if anything failure occurs in the middle of my BCP operation
entire thing is ROLLBACK'd. Then what is the point in taking the T-log
backup as in case of Bulk logged Recovery Model. How it is going to
help me out during my recovery phase?
can Anyone help me out in understanding??
Any practical scenario will be more helpful!!!
Thank You!
For Bulk logged recovery these are the only operations for which logging is not going to happen
1) Creation of index or reindexing
2) Bulk copy operations
3) select into
But where as for other regular DML operations like insert\update\delete are logged operations.
So during bulk logged recovery bulk logged recovery operations are not logged and other dml operations mentioned above are logged, in case of any issues happens when database is bulk logged recovery wecann't recovery database for bulk logged operations , but yes we can recover data modified though DML operations.
July 1, 2009 at 6:00 pm
Kalyani,
I understood what you were saying.Thanks for the scenario you were mentioning. Pretty much similar to the Oracle Archive logging Architecture.
I have 2 more questions here.
How many Virtual Logs does SQL Server 2005 maintains???
And, As a whole how would the T-log backups is helpful for me, if my Database is in bulk - logged recovery model. ???
Any more examples on this !!!
I want dig more on this.
July 1, 2009 at 7:38 pm
Just correcting Kalyani's comment:
Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions.
MJ
July 1, 2009 at 10:41 pm
Right, In Bulk logged mode, bulk logged transactions are minimally logged. all pages which are modified by bulk logged operations are marked. During log backup all extents containing such pages are backed up, which means your log backup file will be huge where as your log file will be smaller in size.
http://msdn.microsoft.com/en-us/library/ms190692.aspx%5B/url%5D
July 1, 2009 at 11:20 pm
I hope, if there was any bulk-logged activities happened , point in time restore will be failing.
"
If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.
"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply