question

Jonathan89 avatar image
Jonathan89 asked

Ola Hallengrens - Output File cleanup does not delete files

We run Ola Hallengrens backup,dbcc & index jobs but we have not scheduled Output File Cleanup for some reason. Noticed this today and when we tried to run it on a server that had 2 or more instances it doesn't delete old logs. We have for example set up 1 server with 2 instances and it's set up with AvailabilityGroups. When running the script this is the output: (I've deleted a few rows so it didn't take up the whole page but it deleted a few files) Date 2017-07-04 10:57:55 Log Job History (Output File Cleanup) Step ID 1 Server SERVER1183\INST01 Job Name Output File Cleanup Step Name Output File Cleanup Duration 00:01:54 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: VT\sqladmin-internaltes. ...8C0E25584E08C4CB393567C2C66083E_1_20170301_132833.txt" del "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x18C0E25584E08C4CB393567C2C66083E_1_20170304_200000.txt" del "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x6DFC82E95... Process Exit Code 0. The step succeeded. After checking in the Logs folder which is located: E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log we still have 12500 files. The jobs are set up as standard when adding Olas jobs, with output file config: $(ESCAPE_SQUOTE(SQLLOGDIR))\IndexOptimize_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt We have not changed the job command at all so the parameters are: 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" The script works fine when it's a single instance or default instance. It's sql 2014 if that's relevant but we have this problem on 2008 & 2012 aswell. Anyone got this figured out or know how to fix this?
sql-server-2014ola-hallengren
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
If you execute this in a cmd window it will echo out the commands it will try to run (it won't actually try the delete). Does this give any error? Do the file names and locations look correct? 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&"
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jonathan89 avatar image Jonathan89 commented ·
If I run the command in cmd nothing really happens, no error messages. Well the path should be correct since the output file config on the jobs is set to put the logs in the default log folder for mssql and the output file cleaning job is pointing to the same location. What I could try though is to manually change the destination location to a different folder to place the logs and rewrite Olas cleaning script to point to that location but I'm not quite sure how to rewrite it.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Sorry I should have said replace **$(ESCAPE_SQUOTE(SQLLOGDIR))** with the actual path of your logfiles
0 Likes 0 ·
Jonathan89 avatar image Jonathan89 commented ·
I tried to run: cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "$E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log" /m *_*_*_*.txt /d -1 2^>^&1') do if EXIST "$E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\%v echo del "$E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\%v&" Doesn't give any errors. Also tried the same way with the del command but it didn't delete any logs
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
remove the dollar signs cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log" /m _**.txt /d -1 2^>^&1') do if EXIST "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\%v echo del "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\%v&"
0 Likes 0 ·
Jonathan89 avatar image Jonathan89 commented ·
Yeap saw that error right when u typed it :) Tried the correct one and got the result: del "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x3D23D55E22B9204 1AA978DF0B22219C5_1_20170612_145253.txt" del "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseBackup_0x3D23D55E22B9204 1AA978DF0B22219C5_1_20170612_145319.txt" del "E:\MSSQL\INST01\MSSQL12.INST01\MSSQL\Log"\"DatabaseIntegrityCheck_0xB630D38 1CD4AED4D8DE706D4047DF59D_1_20170704_92437.txt" Also tried with the delete and it deleted the files correctly. So it actually worked great to just specify the file location instead. Thank you for the help Kev!
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.