SQL Server Performance

Intermiittent errors in Error log file

Discussion in 'SQL Server Log Shipping' started by viral, Apr 25, 2005.

  1. viral New Member

    We have log shipping congfigured between SAPPROD and SAPFO databases (both are on SQl server 2000).<br /><br />However, we are intermittenly receiving the following error in the application error and the errorlog of sql server database :<br /><br />BackupDiskFile:<img src='/community/emoticons/emotion-3.gif' alt=':O' />penMedia: Backup device '\sapfoTranslogsP46_tlog_200504251310.TRN' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.)<br /><br />I have already checked up your forum and I have already implemented the suggesstions posted in the forum :<br /><br />1. Ensure that full path is being used while configure the log shipping (e.g. in my case I was using L: ranslogs and now it has been changed to \sapfo ranslogs, but the problem still continues)<br /><br />2. Enough permissions have been on the folder where the transaction logs are being written. Infact not only the SQL server agent but everybody has written and read permission on the translogs folder.<br /><br />Please help me in getting the issue resolved at the earliest.<br /><br />Regards,<br /><br />Viral Shah
  2. dineshasanka Moderator

    Check this<br /<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q241397&ID=KB;EN-US;Q827452>http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q241397&ID=KB;EN-US;Q827452</a><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by viral</i><br /><br />We have log shipping congfigured between SAPPROD and SAPFO databases (both are on SQl server 2000).<br /><br />However, we are intermittenly receiving the following error in the application error and the errorlog of sql server database :<br /><br />BackupDiskFile:<img src='/community/emoticons/emotion-3.gif' alt=':O' />penMedia: Backup device '\sapfoTranslogsP46_tlog_200504251310.TRN' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.)<br /><br />I have already checked up your forum and I have already implemented the suggesstions posted in the forum :<br /><br />1. Ensure that full path is being used while configure the log shipping (e.g. in my case I was using L: ranslogs and now it has been changed to \sapfo ranslogs, but the problem still continues)<br /><br />2. Enough permissions have been on the folder where the transaction logs are being written. Infact not only the SQL server agent but everybody has written and read permission on the translogs folder.<br /><br />Please help me in getting the issue resolved at the earliest.<br /><br />Regards,<br /><br />Viral Shah<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  3. satya Moderator

    Are you using any third party tools or tape backup facility on top of this log shipping?



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. viral New Member

    Dineshashanka/ Satya,

    Thanks for your reply.

    However I checked the referenced link, but I could not co-relate my problem what the link mentions. Is the correct link you want me to go through ?

    Currently we do not use any 3rd party tool for doing the backup.

    Kindly suggest me as to how further I can remove this problem.

    Regards,

    Viral Shah
  5. satya Moderator

    What is the log shipping restore interval?
    Are you getting this issue at the same Tlog file or intermittently?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. viral New Member

    The log shipping interval is 10 minutes with 0 minutes delay.

    Yes I am getting the issue intermittenly and their is no definite pattern.

    rgds,

    viral shah
  7. satya Moderator

  8. viral New Member

    Hi,

    but this does not tell who is holding the locks on the system ? it only tells whether it is possible to access the file or not.

    Kindly guide me.

    rgds,

    viral shah
  9. satya Moderator

    As a test try increasing the log interval to 15 minutes.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. viral New Member

    Hi Satya,

    What I am going to gain by increasing the frequency ? Is there any advantage for doing it ?

    rgds,

    viral shah
  11. derrickleggett New Member

    Not really. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] You should be able to look at job history to see overlaps. Did you use the maintenance wizards to setup log shipping, or is this something you setup yourself? Are you sure nothing else is holding the file? Download process explorer and see what's holding the file locked.<br /><br /<a target="_blank" href=http://www.sysinternals.com/ntw2k/freeware/procexp.shtml>http://www.sysinternals.com/ntw2k/freeware/procexp.shtml</a><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  12. viral New Member

    Hi,

    Thanks for the update. I have installed the procexp as suggested by you.

    However, against which process I should monitor against. Moreover in the bottom pane, I am not seeing any handles against any process.

    Can u guide me thru this ?

    regards,

    viral shah
  13. satya Moderator

    I have had this kind of issues when the log shipping interval was 10 minutes, when I've increased to 15 mins. the problem was disappered.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. viral New Member

    Satya,

    But where you able to identify what was the problem? there should be something wrong when the error occurs. and why it would disappear when you increase to 15 minutes ?

    I am more concerned what if still does not solve after increasing the frequency to 15 minutes ? do we use try and error method and increase to 20 minutes ?

    rgds,

    viral shah
  15. satya Moderator

    Viral

    I'm not inclining to ignore the error and not to investigate, I intend to say the problem disappeared when we increased the interval value. So we were not concerned about that intermittent error and further time to spend on the problem.

    In your case deploy the process suggested by Derrick and see what is holding the file.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. viral New Member

    Satya,

    Thanks for your immediate response.

    However, as suggested I have deployed that application. But it gives bunch of details and which process to monitor is confusing. So if u could guide me further, that would be real great help to us in solving the problem.

    rgds,

    viral shah
  17. satya Moderator

    I never got a chance to use such tools as I haven't gone that far, if the problem is resolved better utilise time for other important things. May wait for Derrick's response or send him a PM.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page