February 20, 2008 at 10:47 am
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
February 20, 2008 at 11:07 am
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
Learn to play, play to learn !
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 but most of the time this is me
February 20, 2008 at 11:12 am
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
February 20, 2008 at 11:15 am
I tried that in Enterprise Manager and the File name: section of Error Log is greyed out.
I'll try the registry key.
February 20, 2008 at 11:24 am
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!
February 21, 2008 at 5:42 am
"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
February 21, 2008 at 6:12 am
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
February 22, 2008 at 3:26 am
Hi Chris,
you need to stop the agent to be able to change the log settings...
regards
karl
Best regards
karl
September 10, 2009 at 11:02 am
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.
December 18, 2009 at 1:42 pm
Thanks, Antoine Forget-266116!
It worked as you metioned of the SQL Server Agent -> Error Logs -> Configure:-D
January 12, 2011 at 8:26 am
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.
January 12, 2011 at 9:35 am
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.
January 12, 2011 at 9:41 am
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply