SQL Server Performance

Backup problem

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, Apr 16, 2007.

  1. satya.sqldba New Member

    Hello DBA's

    I am having a strange rpoblem in T-log backups. This is my cycle:

    1. Full backup at 12:00 A.M
    2. T-log backups from 2:00 A.M to 10: P.M in the night for every 2 hrs.
    3. full backup at 11:00 P.M
    ...
    again full backup at 12:00 A.M.

    From Sat morning log backups have failed for all the 3 days.
    I checked the error log and this is the error message:

    'BACKUP failed to complete the command BACKUP LOG "D/B name". Check the backup application log for detailed messages.'

    When I tried to backup the log by issuing a query, the message says there is no current backup.

    But all the 3 days I am sure that the full backup at 12:00 A.M was success.

    When I tried running the full backup once again today followed by log backup, it was a success.

    Any idea why my log backups have failed for 3 days?

    Thanks
    Satya

  2. satya.sqldba New Member

    When I tried referring to the following link

    http://support.microsoft.com/default.aspx/kb/921106

    it says that this problem may occur when the internal status flag for a full database backup does not reset after you create the full database backup.

    But this is mentioned with reference to diff backup. Does this happen in case of T-log backups too?
  3. MohammedU New Member

    Query the system tables to see status of all backups..
    Do you see the full backup file, if yes run the restore headeronly to see when it was completed and its details...

    select * from msdb..backupset
    where backup_start_date > getdate()-3
    order by backup_start_date desc

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. satya.sqldba New Member

    Yes Mohammed

    I have already looked into those and the I can see the backup files created that nght at 12:00. I am sure that all the 3 nights, the full backup was successful

    Thanks
    Satya


  5. satya Moderator

    Were there any non-logged operations or any bulk insert type of operations in between the database backups?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. satya.sqldba New Member

    No Satya,

    There were no such operations. Just curious, would that affect this in any way?

    Was this problem beause of something like the internal status flag for full d/b backup not being reset?

    Thanks
    Satya
  7. satya Moderator

    Yes it will be mismatch the LSN between the log backups, if any of such process will attempt to change recovery model on the database will invalidate such log backups between the complete backups.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. MohammedU New Member

    Tlog backups should fail even you had non-logged operations or full backup fails...
    Did you try running the tlog backup in query window to a different drive...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. satya Moderator

    See this KBAhttp://support.microsoft.com/kb/827452 in thsi case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  10. satya.sqldba New Member

    OK

    But the strange thing is I have 4 d/b's scheduled in log backups and all gave me the same error that there is no current backup while the previous night's full backup was success and the backup files are valid.

    Any other reason why this might have happened?

    Thanks
    Satya
  11. satya Moderator

    Checked the KBA above and see whether it meets this criteria?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  12. satya.sqldba New Member

    Ya just looked at it

    I dont think my problem comes under that issue.

    To be more precise from my SQL log:

    Message
    Error: 3041, Severity: 16, State: 1

    It is some what near to the following one:

    http://support.microsoft.com/default.aspx/kb/921106


    I am not sure if the above one is the problem with my case.
  13. satya Moderator

    If it meets the specification on the above KBA then go and submit to obtain the hotfix, fyi there will be a charge for it.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  14. satya.sqldba New Member

    Thanks for the info Satya

    But the KBA has the info with respect to diff backup. I was just wondering if that could even trouble the T-Log backup too.

    Anyway I will wait for 2 or 3 more weeks and if it repeats once again, I will try to apply the hotfix.

    Satya
  15. MohammedU New Member

    For hotfix related to bugs....generally there will be NO charge....
    Any idea why there will charge in this case?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  16. satya Moderator

    Why don't you reproduce the problem in the dev. or test environment to ensure that hotfix may no be required, to save another hassle if it occurs. Differential backups do not allow point-in-time or marked log recovery.

    Fyi
    quote:
    Differential backups will increase the speed of the backup operation as well as the restore. Since only the changed or newly allocated extents (bitmap tracking) are captured, differential backups are faster and smaller than full database backups. Also, in a recovery process, the last differential backup can be restored without applying a series of the individual transaction-log backups or differential backups that took place between the last full database backup and the last differential backup (all changed data is captured in the last differential backup).

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  17. satya.sqldba New Member

    I have tried to see if the problem occurs once again.The past 3 days full backups have been successful and the log backups were failing. And this is the first time that this has occured in the past 1 month of log backups happening.

    But when I tried to run the full backup followed by log backup today morning everything was fine(it does'nt give me the error "current full backup does not exist"). Why is it that the backups at night that could not be recognised while the ones generated today morning recognised...that's strange

    Thanks
    Satya
  18. MohammedU New Member

    Why are you running full backup at 11:00 pm and at 2:00 am?
    Is it anything to do this it?
    How much time each full backup is taking and what is the db size?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  19. satya.sqldba New Member

    Mohammed

    The full backups are running once at 11:00 P.M in the night and once again in one hour at 12:00. I made sure the schedules are not clashing with each other. (The one at 11:00 is to have a particular set of d/b's to a diff server. I did not want them to interfere with my main backup job that is scripted according to a policy of maintaining weekly,monthly etc..)

    It takes hardly 20 mins for the 11:00 P.M backups to complete and on the day the T-log backups started failing it was the same. I checked the SQL log too and made sure the scheduled did not clash with each other.

    Thanks
    Satya
  20. satya Moderator

    If additional issues occur or any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix.

    quote:Originally posted by MohammedU

    For hotfix related to bugs....generally there will be NO charge....
    Any idea why there will charge in this case?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  21. satya.sqldba New Member

    Satya

    I just came to know from one of the developers that there was a bulk operation in one of the d/b's from 5:00 P.M to 11:00 P.M on Friday evening. Would that have been the cause for the problem?

    The log backups started failing from 2:00 A.M Sat morning, but the 12:00 A.M full backup on Saturday was success. So If I am not wrong, the bulk operation is not related to the log backups failing. Correct me if I am wrong.

    Thanks
    Satya



  22. MohammedU New Member

    I don't think non-logged/bulk operation cause this issue unless db option or recovery model changed...

    Is any one changed recovery model of the database?

    You must perform a full database backup before you back up the transaction log for a database in SQL Server 2005 and in SQL Server 2000
    http://support.microsoft.com/kb/928317


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  23. satya.sqldba New Member

    Ok Mohammed

    That's what my perception too was. It takes a valid full backup for the immediate log backup to be a success. Then my error seems to be a strange one. The full backup is success and the immediate T-Log backup is a failure saying that there is no current backup.(that repeated for 3 days because I did not check the jobs over the weekend).

    When I repeat the same process of taking a full backup followed by T-Log backup today morning it is success.

    I will wait and see if the error returns once again in the future...

    Thanks
    Satya
  24. satya Moderator

    NOn logged operations will invalidate the transaction log, minimal logging involves logging the minimal amount of information in the transaction log that is required to recover the transaction without supporting point-in-time recovery.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  25. satya.sqldba New Member

    Yes Satya

    I understood what you said, but the full abckup at 12:00 A.M on Sat should be enought to take a log backup at 2:00 A.M on Sat while the bulk operations where between 5:00 P.M and 11:00 P.M on Fri

    Thanks
    Satya

  26. MohammedU New Member

    Satya,
    Are you sure? NOn logged operations will invalidate the transaction log in sql 2005?
    I think it was true in sql 70 but not 2000 and laster...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  27. satya Moderator

    I believe so, as I have discussed this with Kimberly Tripp earlier as she referred one of the blogs too. I will confirm it back after checking it again.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  28. Roji. P. Thomas New Member

  29. satya.sqldba New Member

    Mohammed and Satya

    I think I know where the problem is. There is also a step in the full backups that truncates the log files immediately after the full backup. It is causing the log to be invalid after the full backup. Correct me if I am wrong.

    If that is what causing the log backups to fail, what would be the best time to truncate the log files?

    Thanks
    Satya
  30. MohammedU New Member

    If you have the step to truncate the log then your log backup will fail and you should get the following error...
    and you test with pubs db...

    Msg 4214, Level 16, State 1, Line 1
    BACKUP LOG cannot be performed because there is no current database backup.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP LOG is terminating abnormally.



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  31. MohammedU New Member

  32. satya.sqldba New Member

    Yes Mohammed,

    Thats the exact error I got, so these 2 things cannot happen together: truncating the log files and immediate log backups.

    I am thinking of a better strategy towards this, scheduling to truncate the log at a different time or not doing so frequently.

  33. MohammedU New Member

    Why do you want to truncate the log?
    If you truncate the log you are effecting your poin in time recovery...

    If you want to truncate the for any reasons and not worried about the recovery truncate the log before full backup....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  34. satya.sqldba New Member

    Mohammed

    How would that effect point in time recovery in any way? I was truncating the log only after the full backup.Also I have the hard drive that has full backups and log backups backed up onto tapes on daily basis. So if I wish to go back I can, right?

    If I have the full backup and the succeding transaction log backups until the next backup (this happens on daily basis), I can go back to any point in time recovery. Would truncation of transaction log file effect this process in any way? Tell me if there is any flaw in my perception.

    Thanks
    Satya
  35. MohammedU New Member

    I don't think you can use STOPAT option with full/diff backups incase if you want to restore your backup..

    11:00 pm - Full backup started at ...
    11:10 pm - Some delete the data
    11:20 pm - Full backup completed
    11:21 pm - Log truncation.
    11:30 pm - realized the deletion and want to restore as of 11:09 pm.

    How do you do it? if you truncate the log.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  36. satya.sqldba New Member

    Ok

    Got it, so the concern is with the transactions during the backup process.

    And yes I agree with u that we cannot do in point recovery with full/diff backup. But I can still do in point recovery in my case because I am having my T-log backups every 2 hours.

    But the situation you have picturised is crystal clear of how the truncation of log might effect in-point recovery, I was not thinking about such situations (transactions during the backup). Now I will have to think about something other than truncating the log daily.

    Thanks
    Satya
  37. MohammedU New Member

    You didn't answer one more question [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Why you want to truncate the log?<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  38. satya.sqldba New Member

    I never wanted to do that, my IT director wanted to. I was telling him about the consequences, but he was so much pressing on that to happen.
  39. MohammedU New Member

    I know sometimes you get this type of managers specailly when they are half knowledge..<br /><br />Did he give any reason for that?<br /><br />Good luck [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  40. satya.sqldba New Member

    He was concerned about the log growing too large and the system getting hanged. I told him that I could monitor the log usage and truncate when it really grows too large. BUt he was pressing me for the other way.

  41. satya.sqldba New Member

    I don't think any DBA would want to have the log files truncated immediately after a full backup, right?

    If so, what would be the best way to monitor the space the transaction log file occupies on the hard drive? Do you also suggest looking manually into how large is the log...I don't think thr is any other way around to do this.

    Thanks
    Satya
  42. MohammedU New Member

  43. satya.sqldba New Member

    You did not answer my previous question: [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I don't think any DBA would want to have the log files truncated immediately after a full backup, right?<br /><br />I think with all the log backups failing ,now I have enough explanation to convince him not to have the log files truncated daily... [V]<br /><br />Thanks<br />Satya
  44. MohammedU New Member

    Yep, you got the evidence [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] prove him wrong...<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  45. satya Moderator

    I believe you have got most of it from Mohammed, as suggested attempting the truncate of log will have that mismatch that you have seen the consequences.<br /><br />Ask your Director to have a thorough read on BOL about transaction log architecture, in any case enusre to control the transactions, perform frequent log backups and if disk free space is not an issue set a sensible (high) size to transaction log to accomodate the activities on the day to day basis. If he still wanted to go in same route ask him to call us [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] for a brain wash.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
  46. satya.sqldba New Member

    Ok Mohammed and Satya<br /><br /> Thanks for ur suggestions [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /> Satya
  47. satya.sqldba New Member

    I implemented a work around for this.

    I have configured alerts to monitor the log space and execte a job of truncating the log files when they grow beyond a specified amount of space.

    I guess this is a good solution for my situation

    Thanks
    Satya
  48. MohammedU New Member

    Post the log space monitoring script, if any body wants use it and thanks for the feed back.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  49. satya.sqldba New Member

    Ok Mohammed<br /> <br /> Here is the script of the alert that I have created:<br /><br />USE [msdb]<br />GO<br />/****** Object: Alert [Truncate log_AICETransfer] Script Date: 04/18/2007 16:00:23 ******/<br />EXEC msdb.dbo.sp_add_alert @name=N'Truncatelog_AICETransfer', <br />@message_id=0, <br />@severity=0, <br />@enabled=1, <br />@delay_between_responses=0, <br />@include_event_description_in=1, <br />@category_name=N'[Uncategorized]', <br />@performance_condition=N'MSSQL$SQLPROD02<img src='/community/emoticons/emotion-2.gif' alt=':D' />atabases|Log File(s) Size (KB)|AICETransfer|&gt;|10000000', <br />@job_id=N'449b07fe-f6c6-48c5-ba42-562b3d6216b6'<br />GO<br />EXEC msdb.dbo.sp_add_notification @alert_name=N'Truncate log_AICETransfer', @operator_name=N'Satya', @notification_method =1<br /><br />Hope this helps<br />Satya

Share This Page