SQL Server Performance Forum – Threads Archive

Intermiittent errors in Error log file

We have log shipping congfigured between SAPPROD and SAPFO databases (both are on SQl server 2000). However, we are intermittenly receiving the following error in the application error and the errorlog of sql server database : 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.) I have already checked up your forum and I have already implemented the suggesstions posted in the forum : 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) 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. Please help me in getting the issue resolved at the earliest. Regards, Viral Shah

Check this http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q241397&ID=KB;EN-US;Q827452</a> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by viral We have log shipping congfigured between SAPPROD and SAPFO databases (both are on SQl server 2000). However, we are intermittenly receiving the following error in the application error and the errorlog of sql server database : 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.) I have already checked up your forum and I have already implemented the suggesstions posted in the forum : 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) 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. Please help me in getting the issue resolved at the earliest. Regards, Viral Shah <hr height="1" noshade id="quote"></blockquote id="quote">

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.

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

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.

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

May be you need some monitoring software on operating system to check why that file is used by other process. You can check this linkhttp://www.databasejournal.com/features/mssql/article.php/10894_3492046_2 to make use of it. HTH 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.

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

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.

Hi Satya, What I am going to gain by increasing the frequency ? Is there any advantage for doing it ? rgds, viral shah

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. http://www.sysinternals.com/ntw2k/freeware/procexp.shtml</a> MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

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

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.

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

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.

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

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.

]]>