September 6, 2011 at 4:35 am
Hi All,
In my environment I have a SQL Agent job that runs a stored procedure that internally iterates and pauses using WAITFOR. The intention is that at certain (unpredictable and frequent) times, code is required to execute at least one per second, but that the code may pause for up to 15 minutes when not needed (again, at unknown/unpredictable times).
The Stored Procedure is invoked using a CmdExec step via SQLCMD.
If an error occurs that causes the job step to complete, a second step notifies an administrator, and then restarts the first step - the intention being that the job never "Ends".
So far, this has worked fine in production.
My problem, however, is in the log file for this job - the procedure outputs periodic status messages that i need to capture in a log file.
The job step configuration is set to output to a text file, which is datestamped using the STRTDT Token as follows (with the actual path and job name inserted, obviously):
<Logging Directory>\SQLAgent Job Log - <Job Name>_$(ESCAPE_SQUOTE(STRTDT))_Step$(ESCAPE_SQUOTE(STEPID)).txt
The problem is that the job continuously writes to the same file, as the STRTDT variable is the start time of the Job. This results in a single, ever growing log file.
Ideally, I need to find a solution to cause the log file to "roll over" to a new log file each day, without having to stop and start the job (As it is unpredictable when would be a suitable time to stop and start the job, it would be a manual process to check, and I don't want to rely on a manual process here).
There is little I can do to change the way this works from a process perspective.
Does anyone have any ideas?
September 6, 2011 at 4:42 am
You could try logging the output of your job to a table instead of to a file. Then you can query the table, remove rows that you don't need, and, if you still need a file that you can touch, you can export from the table.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply