May 16, 2008 at 12:27 pm
My guess, and it is only a guess at this point, since I have not done the research, would be that it only truncates from the log if it is committed AND isn't needed for some other recovery process (i.e., needed in the backup process). My guess is it would stick around at least until the backup process is finished, then it could release it. Again, only an educated guess.
May 16, 2008 at 12:33 pm
yep, that sounds the most likely. a big enough database and judicious use of dbcc sqlperf(logspace) could prove it.
---------------------------------------------------------------------
November 19, 2008 at 7:40 pm
Hi
Found this on Microsoft technet
http://technet.microsoft.com/en-us/library/ms345414.aspx
"Data Backup Operations and Restore Operations
Log truncation cannot happen during any backup or restore operation. In SQL Server 2005 and later versions, log backups can occur during a data backup. However, log truncation cannot occur during such log backups, because all of the transaction log must remain available to the data backup operation. If a data backup is preventing log truncation, canceling the backup might help the immediate problem.
For more information about log truncation, see Transaction Log Truncation. "
November 23, 2008 at 3:32 am
it can backup log during database backup but cannot truncate the log file
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2009 at 9:23 am
restore the logs AFTER the DB completed. Obviously you can't trucate them until its done.
MS consistantly says "AFTER" the db backup completed.
January 30, 2009 at 9:40 am
I am curious if using the third party tools makes any difference with this. One of our systems essentially does this every night, since we start our Fulls at X and then we have a Log that starts at X + 5 minutes both using Litespeed.
With this system (SQL 2000), the log is blocked until the Full completes and then the log itself completes.
The LSN's are as follows (first row is full, second row is log)
FirstLsn LastLsn CheckpointLsn
104351000005544100001 104351000005750300001 104351000005633400013
104351000005544100001 104351000005750000001 104351000005633400013
Obviously the LSN's are identical other than the LastLSN on the log being lower than the LastLSN on the full. Not really sure what all this tells me, but it is interesting.
I am sure this has already been all determined but I thought I would throw the LSN's up there just to show some actuals.
August 17, 2011 at 3:23 am
Hi,
my full and transaction log backups may also run concurrently. While all the technical details discussed in this thread are definitely interesting, my biggest worry is:
Will the recovery process be sucessfull in any case?
Situation 1: Transaction log backup ran before full backup. At restore, I need to restore the full backup only. If I try to restore the transaction log, I will probably get an error message indicating that the transaction log contents is too old. Fine.
Situation 2: Transaction log backup ran after full backup. I restore the full backup first and the transaction log backup afterwards. Fine.
Situation 3: Transaction log backup and full backup ran concurrently. So maybe the transaction log backup will contain transactions which are also included in the full backup, and maybe the transaction log will contain newer transactions not included in the full backup. So, if I restore the full backup first and then the transaction log backup, will the restore process be able to find out what parts of the transaction log it needs to restore?
Cheers,
Nang.
February 25, 2012 at 10:47 am
Sir,
I need your help recommendation. I have a database in SQL 2008 and size of the database is around 400gb and the transaction log around 120gb (growing very heavily). If i create a schedule job for transaction log backup, i was getting an error. Is it necessary i have to take a full backup first and then only the transaction log can be taken or only taking backup of transaction log is possible?
kindly advise
Srini
February 25, 2012 at 10:57 am
rsrinivasan-1006867 (2/25/2012)
Sir,I need your help recommendation. I have a database in SQL 2008 and size of the database is around 400gb and the transaction log around 120gb (growing very heavily). If i create a schedule job for transaction log backup, i was getting an error. Is it necessary i have to take a full backup first and then only the transaction log can be taken or only taking backup of transaction log is possible?
kindly advise
Srini
Srini,
To take transaction log backups the database must be in the FULL or BULK RECOVERY model and you must take a FULL Backup first. Remember when developing a backup plan that you have to plan for recovery as well. You do not want to take 1 full backup and then just transaction log backups because you would have to restore the full backup and then ALL the transaction log backups after it until the point in time you need to recover to which will take a long time and eventually you'd run out of space to keep all those log backups.
Pick a regular schedule for full backups and then use a combination of differential and log backups that will allow you to meet your SLA's and RTO (recovery time objective).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2012 at 11:05 am
Sir
thank you for the reply. Can you provide me the steps to be carried, if i wanted to take a backup of only Trasnaction log with different schedule. (without taking full backup because the time which was taken for the fully backup was very high. Our client wanted to trucate only the transaction log).
if you have any other method of truncating transaction log only periodically will help ful. (sql server 2008). in Sql server 2005 i have a script i use to create a job for that. but in sql 2008 the same script is not working . (truncate only feautre not available sql 2008). kindly advise.
February 25, 2012 at 11:15 am
Please read through this - Managing Transaction Logs[/url]
If you're in simple recovery then you don't need a log backup to truncate the log, it happens automatically. If you're in full recovery but have never ever taken a database backup, you also don't need a log backup to truncate the log (the DB behaves like it is in simple recovery until a full backup is taken)
Now, you didn't say what error you got from taking a log backup, neither did you say what recovery model the DB is in, so it's hard to advise further, but please do read that article.
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
February 25, 2012 at 11:21 am
Dear Sir
Sorry for the incomplete information.
the db is in full recovery mode. my query is i wanted to truncate the transaction log periodically thru maintenance plan. Can it be possible. what is the procedure to be carried. The db size huge.
February 25, 2012 at 12:08 pm
Please go and read that article.
To allow log reuse in full recovery you need log backups. If you don't care about point-in-time restores (as appears to be the case considerign your comments about backup log ... truncate_only) then switch the DB to simple recovery model and the log will manage itself without the need for any maintenance plans.
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
February 25, 2012 at 8:53 pm
Ok. Thank you for the reply.
June 4, 2012 at 4:47 am
HI
1.The current organisation which i works has a major server which is running in cluster with logshipping
2.In this for every 10mins transactional will generates as per the schedule tasks from logshipping
3.In this it has scheduled full backup at 10Am and diffrential backup at 6pm daily and one backup after executing its size is nearly 60gb parellely the logshipping also executes its log(.trn files ) to the drive in which it is configured .so from my veiw it is possible in 2005 and 2008 sql version with out any disturbencess if any one have better answer plz share if any mistakes in tis reply plz suggest for better knowledge
THnaks & regards
NAGA.ROHITKUMAR
Thanks
Naga.Rohitkumar
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply