1

I am using the ola-hallengren maintenance scripts. I am logging to table and file. I want to change the location of the file logs. Can I do this? Many thanks

5

Changing it per jobstep while keeping Ola's log naming convention

You can change the location in the job step properties, opened with either double clicking or pressing Edit.

enter image description here

enter image description here

Select Advanced

enter image description here

And go to Output File

enter image description here

You will notice a string like:

$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseIntegrityCheck_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Where you can change the $(ESCAPE_SQUOTE(SQLLOGDIR)) to your preferred location If say, you would like to get the logs in C:\temp, then change it to:

C:\temp\DatabaseIntegrityCheck_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

The string can vary depending on the purpose of the job step and your SQL Server version.

(SQL Server 2012 and above should get the same result)

As a result, Ola's naming convention remains the same and log files will not be reused.

enter image description here

You have to remember to adapt the OutputFileCleanup Job

From:

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "$(ESCAPE_SQUOTE(SQLLOGDIR))" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v

To:

cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "C:\temp" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "C:\temp"\%v echo del "C:\temp"\%v& del "C:\temp"\%v"

Or in short, change the $(ESCAPE_SQUOTE(SQLLOGDIR)) parts to your specified folder.


Changing it for all the jobs created by the MaintenanceSolution

You can do this for the entire maintenance solution by adding the folder name to the @OutputFileDirectory parameter in the MaintenanceSolution.SQL script.

SET @OutputFileDirectory = NULL         -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.

E.G

SET @OutputFileDirectory = 'C:\temp'

Validate the changed folder:

enter image description here

If the jobs already exist, then you would have to recreate them, described below.


If the MaintenanceSolution jobs already exist

If you need to change the log location for all the existing jobs, then you would have to update each of the steps, or drop the jobs and recreate them with Ola's script.

(Remember that scheduling, job state, ... WILL be removed from the job when dropping and creating them).

Example query to drop the jobs:

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DatabaseBackup - SYSTEM_DATABASES - FULL')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseBackup - USER_DATABASES - DIFF')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - USER_DATABASES - DIFF'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseBackup - USER_DATABASES - FULL')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - USER_DATABASES - FULL'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseBackup - USER_DATABASES - LOG')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - USER_DATABASES - LOG'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseIntegrityCheck - SYSTEM_DATABASES')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseIntegrityCheck - USER_DATABASES')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseIntegrityCheck - USER_DATABASES'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'IndexOptimize - USER_DATABASES')
EXEC msdb.dbo.sp_delete_job @job_name=N'IndexOptimize - USER_DATABASES'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sp_purge_jobhistory')
EXEC msdb.dbo.sp_delete_job @job_name=N'sp_purge_jobhistory'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sp_delete_backuphistory')
EXEC msdb.dbo.sp_delete_job @job_name=N'sp_delete_backuphistory'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'CommandLog Cleanup')
EXEC msdb.dbo.sp_delete_job @job_name=N'CommandLog Cleanup'

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Output File Cleanup')
EXEC msdb.dbo.sp_delete_job @job_name=N'Output File Cleanup'

Validate and rerun the Maintenance solution with

SET @CreateJobs = 'Y'

  • Fantastic. I will edit each job as I want to change the location depending on the job so there is a file structure. Also I don't want to have to set all schedules again Will the cleanup job handle file structure like x:\logs\index, x:\logs\log_backup and x:\logs\full_backup etc? x:\ being original location just more structured now. All were being saved directly to x:\ and I want to add structure. Many thanks – Round Mar 20 at 15:14
  • @Round No problem! I am not 100% with you here, the most important one to change is the Output File Cleanup job. If all logs are in x:\logs then simply change the output files in the jobsteps, and change the job, again replacing $(ESCAPE_SQUOTE(SQLLOGDIR)) with x:\logs. This should cleanup the logs for all the jobs. If you are going to put the job step outputs in different folders, then you might have to run the commands from the ` OutputFileCleanup` job a few times, for each folder (or change the command in an other way). – Randi Vertongen Mar 20 at 15:29
1

Yes, you can do this. This is the way I've done it in the past:

  1. Select SQL Agent Job you want to change location of file logs
  2. Steps > Edit > Advanced
  3. Change the file path in the "Output file" section to where you want it to save to. You probably need to create the new folder structure ahead of time in the new location before setting the new path.

There might be a better way to change them all at once but I haven't done this before.

  • Remember to also change the CommandLog cleanup job for this. – George.Palacios Mar 20 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.