DB Log File Location Error

  • Hi,

    I am taking full backup of db on remote location. The issue we are facing is that my backups are generating fine at remote address but while generating Log file for those backups, Maintenance plan history shows error that It cannot locate part of address. How this is possible that my backups are generating with same credentials but while generating log file it shows credentials error. Attached is the screenshot for maintenance plan history.

    Attachments:
    You must be logged in to view attached files.
  • you need to post the error message in full (do it in plain text)- it is chopped off on your print screen

    this is step 2 of that job - your step 1 appears to work - but we need to know what step 2 does - just script the entire job out (right click on the job in sql agent and find the script option) and we'll be able to help.

    you've used the word "log" a few times - do you mean transaction log? or a log file for the backup operation? - if it is the latter then maybe the log files are going to a place that the sql agent service account has no permissions for.

    MVDBA

  • It seems odd to me that the path name in the error seems to have the slashes doubled.  it says:

    I:\\CM\\MaintenancePlan2_Subplan...

    when I expect it should say:

    I:\CM\MaintenancePlan2_Subplan...

     

  • I also saw those double smashed and tested it... It works fine

    MVDBA

  • Hopefully the OP will get back to us with more information then on the commands used in the job.

  • Thanks everyone.

    Let me clear things first.

    1. Log means the log file generated on backup operation while taking db backup. It is made with every backup. Its name would be like MaintenancePlan_Subplan_1_20191017105932.txt

    2. Full error is ( Could not find a part of the path 'I:\\CB\\MaintenancePlan2_Subplan_1_20191017102732.txt' ). Actually the path is ( I:\CB ) for backup location and for log file location. Backup files are generation at same address but the log file is not. This is a remote address. I used (EXEC XP_CMDSHELL 'net use I: \\10.3.0.35\daily_backup /user:administrator xxxxxx') command to add location ( I ) as remote address. I also used it without subfolders CB but the error is same. KEEPING IN MIND THAT WITH SAME ADDRESS, BACKUP FILES ARE GENERATING FINE.

  • Being honest - why bother with the log file?

    I have an operator and alerts set up for all jobs... I also have a script that checks the last run status of all jobs on all servers. Nobody ever checks every log file on every server (do they?)

    for me, the log files are just something that I need to clean up.  If you want to work smart, build a tiny app (maybe a web page or excel spreadsheet) that runs a query against msdb and collects the data for all of your servers

    and beware with log files - they are finally written on completion - if a job is still running 3 days later then you might get some missinformation

    MVDBA

  • Thanks @mvdba

    But for me the log file is very important to check what went wrong. Its sort of legal nature you know. And the site is quite big and is daily running job. We have a years's data that can be called even after months. So this is important to us.

    Thanks

     

  • as a test, try writing it to a UNC path rather than a mapped drive

    \\servername\sharename\filename.txt

     

     

    MVDBA

  • Good point, if I: is a mapped drive of a network share, the mapping may not exist for the user the backup job is running as.  UNC path name is just a mater of ensuring the permissions exist.

    I believe MVDBA's point about generating a log file, is that the SQL Server Agent jobs themselves that are a part of the Maintenance Plan will have a job history in them already stored in msdb system database.  If you are concerned about how long those are kept for legal reasons, you can change SQL Agent's default behavior for it's logs.

    I typically turn off the option "Limit size of job history log" in the SQL Server Agent Properties on the History page.  Then you can use a separate scheduled job that runs sp_purge_jobhistory with the @oldest_date parameter, to remove old records.  There is also an option within the Maintenance Plans themselves:

    https://troubleshootingsql.com/2009/12/30/how-to-purge-msdb-history-using-t-sql-scripts/

  • Chris Harshman wrote:

    Good point, if I: is a mapped drive of a network share, the mapping may not exist for the user the backup job is running as.  UNC path name is just a mater of ensuring the permissions exist.

    DOH!!!!!!! I didn't think of that... I was just throwing out ideas and seeing what might work- good shout chris!

    MVDBA

  • Thanks @chris-2 Harshman & @mvdba

    While assigning the UNC address the error is "The user name or password is incorrect." <b>My point here is that why .bak files are generating at same remote address but txt log files are not in maintenance plane job ? </b>Keeping in mind is that when i change the location to system drive then it works fine (both bak and txt files are generating) but when i use mapped drive or URL address then only bak files are generating and txt log files are not for maintainance plan jobs. Also if there is user rights permission or address issue then the same error should be displayed for .bak files too but why it is only giving error for txt file. I hope there must be a strong reason or hack for this.

    Thanks

     

    • This reply was modified 4 years, 5 months ago by  na_ma_cool.
  • then it's clear that the 2 steps in the job are operating under different security contexts.

    try this - is your sql agent service running as a domain account ? (if it's NTsystem or a local account then that doesn't count)

    if not then create a domain service account for SQL agent  - same for SQL server

    now grant permissions to the share for those services - I normally go for full permissions and start restricting it until it breaks (then I know the minimum it needs)

    the fact that it is .txt or .bak is irrelevant, it's the process that is failing to connect - perhaps one is using the sql agent account and one is using the sql server account

    MVDBA

  • Yes @mvdba, I tried both accounts that is logged with sql sa account and logged with windows authentication mode but that didn't worked. I also even tried on a new system with default sql settings and tried for both type of account but the issue was same. You are right there must be a permission issue and there must be two processes for generating .bak files and .txt files separately. This is a default behaviour of sql server 2014 for generating .bak and .txt files for remote addresses under 1 maintenance plan. So now i can't locate the process for txt file separately as there is only 1 way to update rights for maintenance plan job which executes both .bak and .txt file generation. I googled allot over web but cannot find a solution for this up till now 🙁

    Also is there a way to alter the default behavior of this sql process?

  • Per other posts, it most likely a permissions issue.  Assuming you have separate accounts for SQL Agent and SQL Server the database backup files are create using the SQLServer account.  Logs/Maintenance files are created using the SQLAgent account.  You can verify it by checking the default backup and log folders.

    Probably the easiest way assuming you have the passwords is to log onto the server using the service accounts.  Using the following

    runas /user:domain/serviceaccount "cmd"

    Navigate within CMD to the specified path and create the files/folders to verify read/write access.  I concur with the other post(s) that you should use UNC and not use drive mappings.  You would need to add the SQLAgent and SQLServer service accounts to the share and file/folders.

     

     

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply