Need to move the SQL Server Agent error log to a new drive

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    I need to move the SQL Server Agent error log a clustered SQL Server 2000 instance to another windows drive. I can find plenty of info on how to move the Server error log, and I can even find articles mentioning that you can move the agent's error log, but I can't find instructions on how to do it.

    Does anyone know the procedure?

    TIA,

    Chris

  • Johan Bijnens

    SSC Guru

    Points: 134265

    If I'm correct that info is stored in a sqlagent registry key.

    Maybe you can find it if you analyze msdb.dbo.sp_get_sqlagent_properties.

    I don't know if changing that key value even would actualy have the

    effect you want it to have.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Carl Federl

    One Orange Chip

    Points: 25384

    The SQL Agent error log location is in the registry and for a default instance, has a key of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\ErrorLogFile

    This is near the key for the SQL Server parameters.

    Enterprise Manager provides a interface to change this under "Management", "SQL Server Agent" - then right-click to get the menu and select "properties" where the log location will be on tab "General"

    SQL = Scarcely Qualifies as a Language

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    I tried that in Enterprise Manager and the File name: section of Error Log is greyed out.

    I'll try the registry key.

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    Wait a second. Carl Federl, there's not mention of an instance name in that path. This is clustered, so I'd hate to mess it up.

    Any idea what the microsoft approved methodology for moving this? I've searched everywhere I can think of and it is not documented.

    Thanks for the help!

  • Carl Federl

    One Orange Chip

    Points: 25384

    "there's not mention of an instance name in that path."

    Please read my post carefully - "for a default instance" is what I wrote, meaning that a named instance will have a different key.

    "I tried that in Enterprise Manager and the File name: section of Error Log is greyed out. "

    Do you have the SQL Server sysadmin role ?

    Do you have Windows local administrator rights?

    SQL = Scarcely Qualifies as a Language

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    Sorry, I missed that in the first post. And yes, I am logging into the server with full local admin rights and have SA on the database server.

    I thought it was interesting that the option was greyed out, but I've had a similar problem with the email setup on this cluster as well. I didn't build it, so I don't know what is causing the quirks. That's why I always prefer to build the clusters myself.

    Thanks,

    Chris

  • Karl Klingler

    SSCertifiable

    Points: 5868

    Hi Chris,

    you need to stop the agent to be able to change the log settings...

    regards

    karl

    Best regards
    karl

  • Antoine Forget-266116

    SSC Enthusiast

    Points: 145

    If you right click on sql server agent and choose properties, then the text box is grayed out.

    If you right click on error log whithin the sql server agent node and choose config then you can edit.

  • Sunny1

    SSC Eights!

    Points: 871

    Thanks, Antoine Forget-266116!

    It worked as you metioned of the SQL Server Agent -> Error Logs -> Configure:-D

  • john.schenck

    SSC Veteran

    Points: 296

    Thank you!

    What a silly interface quirk - can modify the log location by 'configure' of the logs but not for the agent itself even though it's listed right there. I'm sure there is a reason behind it but reason eludes me here!

    Thanks for this again. I personally like all my log files to go to <DRIVE>:\MSSQL\LOGS - as a standard approach to all my SQL Server installs. Also all my data files go to <DRIVE>:\MSSQL\DATA and all disk backups go to <DRIVE>:\MSSQL\BACKUPS. Most these systems have separate file systems for mdf/ndf, ldf and .bak/.trn files. I put log files on the same file system with backups.

  • Antoine Forget-266116

    SSC Enthusiast

    Points: 145

    Not sure of the reason it is not feasible through the SSMS GUI for the error log but as some posted before me it is also possible to modify the error log location.

    --stop the service

    in sql server configuration manager:

    right click on the servic of the sql server you want to modify. Select “properties”

    In the properties screen, select the “advanced” tab

    --modify the log position

    Modify the line contained at “startup parameters”.

    The part of the line to modify is the value after the –e.

    --restart the service

    Once this is done, restart the sql server service.

    If you enter erroneous location in the startup parameters, your service will fail to restart so make sure you keep a copy of your original string in case.

  • john.schenck

    SSC Veteran

    Points: 296

    Yep! That's how I move the master database as well. Modifying the startup configuration parameters using the SS Config Manager works well for me. I was having trouble finding the place to modify the SQL Agent log location. The startup parameter has an editable parameter "Dump Directory" but that doesn't specify the ERROR_LOG location. As stated above modify the "configuration" for "Error Logs" under the Agent in SSMS.

Viewing 13 posts - 1 through 13 (of 13 total)

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