Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2011-08-10 : 15:28:41
|
| Ok, we have developers that create business jobs and leverage SQL Agent to run them. The owner of the jobs is a non-sysadmin user (cheers so far). However when they attempt to run a job that has a log file specified they receive the error "Writing to log files is only allowed to jobs that are owned by sysadmin. Please consider writing log to table.".Ok, so I look into the "Log to table" and "Append output to existing entry in table" options. Turns out the overhead of these options being on can get pretty high. Yes, I've allocated 5 GB for the msdb database on fast disks (actually, high speed disks on a NetApp storage device).So, I look into this some and determine MS did a terrible job at implementing this option. By turning on this option on it creates one entry per job step in the sysjobstepslogs and maintains a field defined as nvarchar(max) across all executions of the job step.So I have two options. I can only turn on the "Log to table" option which will only maintain the most recent job log or I can also turn on the "Append..." option and have one large text field that needs to be parsed. Yes, I can use the sp_help_jobsteplog system procedure but that takes minutes to query. Seems odd to just view a log.So, my question is this, has anyone found other ways to record job output easily and enable end users (techy types but no sysadmin access) to easily view the logs? |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2011-08-10 : 15:53:40
|
| I should probably add that I found the flags field on the sysjobsteps table and found the value of 32 means "Write all output to job history". Now this sounds like what I want, however I have not identified what it really does. No options in the SSMS interface set that value and if I set that value through sp_update_jobstep the job does not appear to do anything additional (including no additional logging).So if anyone has any insight here that could be helpful I'd appreciate it. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-08-11 : 03:48:52
|
| Ok, this might sound like a dumb@ss suggestion but how about changing the owner of the job to a sysadmin account...?? That's actually what I always do to prevent problems with jobs not running because some developer quit or something...- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-11 : 08:01:20
|
| That's what I do too.Further, we don't let developers write SQL Agent jobs. They can make requests from the DBA team, and we'll implement. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2011-08-12 : 01:22:24
|
| Unfortunately neither of those suggestions are feasible. They do maintain specific jobs and the owner of those jobs cannot be a sysadmin account. If the developers want to use SQL Agent as their job scheduling system then so be it. Let them also be responsible for any errors related to those jobs.Any other thoughts? |
|
|
|
|
|
|
|
|
Infamous "Writing to log files is only..." error