February 9, 2009 at 11:40 am
Here is the script as generated from the server. Please set the proper login if you use this, as well as the path to your job log files.
USE [msdb]
GO
/****** Object: Job [Delete Maintenance Report Files] Script Date: 02/09/2009 13:35:42 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 02/09/2009 13:35:42 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Delete Maintenance Report Files',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This is a stop gap for the bug in SQL 2005 SP2.',
@category_name=N'Database Maintenance',
@owner_login_name=N' ',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete Files] Script Date: 02/09/2009 13:35:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Files',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'ActiveScripting',
@command=N'Option Explicit
Const filePath = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\"
main
Sub Main()
ClearArchive
End Sub
Sub ClearArchive()
Dim fso ''As Scripting.FileSystemObject
Dim fld ''As Scripting.Folder
Dim f ''As Scripting.File
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(filePath)
For Each f In fld.Files
If Right(LCase(f.Name), 4) = ".txt" Then
If DateDiff("d", f.DateCreated, Date()) > 14 Then
fso.DeleteFile f
End If
End If
Next
Set fld = Nothing
Set fso = Nothing
End Sub',
@database_name=N'VBScript',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090105,
@active_end_date=99991231,
@active_start_time=174500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Beth Richards
Sybase, Oracle and MSSQL DBA
February 9, 2009 at 12:12 pm
Yes, it did delete my txt files.
Thanks, bethrich. Appreciate it.
February 9, 2009 at 2:19 pm
Ol'SureHand,
You probably have Cumulative Update 2 or higher on top of SP2. Prior to that update, the SSMS maintenance cleanup task - and the corresponding undocumented stored procedure - would not delete any job log that started with the line "NEW COMPONENT OUTPUT" (which all of my SSMS job logs contained).
February 9, 2009 at 4:16 pm
larry Hennig (2/9/2009)
Ol'SureHand,You probably have Cumulative Update 2 or higher on top of SP2. Prior to that update, the SSMS maintenance cleanup task - and the corresponding undocumented stored procedure - would not delete any job log that started with the line "NEW COMPONENT OUTPUT" (which all of my SSMS job logs contained).
That's right, the cumulative "hotfix" is installed as below ... (time for SP3 when I can...).
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3259
Report was generated on "MYSrvr".
Maintenance Plan: MyDailyPlan
Duration: 02:48:56
Status: Succeeded.
Details:
Clean Up History (MySrvr) [msdb etc]
Cleanup history on Local server connection
History type: Backup,Job,Maintenance Plan
Age: Older than 4 Weeks
Task start: 2009-02-09T22:18:53.
Task end: 2009-02-09T22:18:53.
Success
..
MCleanupLogs (MySrvr)
Maintenance Cleanup on Local server connection
Cleanup Maintenance Plan report files
Age: Older than 14 Days
Task start: 2009-02-09T22:19:00.
Task end: 2009-02-09T22:19:00.
Success
January 19, 2018 at 8:43 am
DBA in Unit 7 - Friday, February 6, 2009 12:05 PMI had the same issue and modified a script found online about deleting old files, now it is in my monthly cleanup job.Execute [dbo].[usp_DeleteOldMaintenancePlanLogFiles] 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\', 30SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_DeleteOldMaintenancePlanLogFiles] -- 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\', 17 @basedir nvarchar(255), @days_old_to_allow int = 30--***Enable XP_CMDSHELL to make this work.***ASBEGIN SET NOCOUNT ON declare @mtime datetime declare @file nvarchar(255) declare @fullpath nvarchar(255) declare @daysold int declare @cmd nvarchar(255) CREATE TABLE #t_dir ( InLine varchar(250)) -- Get a directory listing and Insert into #t_dir SET @cmd = 'dir "' + @basedir + '" /A-D' -- /A-D, no directories INSERT INTO #t_dir EXEC master.dbo.xp_cmdshell @cmd-- Clean up unwanted rows in the table DELETE FROM #t_dir WHERE InLine like ' %' or InLine = '' or Inline IS NULL--Put the to-be-deleted filenames into cursor DECLARE c_files CURSOR FOR SELECT convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,18) ) as dtime, rtrim(substring(InLine, 40, len(InLine))) as filen, datediff(dd, convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,18) ) , getdate()) as daysold FROM #t_dir WHERE rtrim(substring(InLine, 40, len(InLine))) like '%plan%' and datediff(dd,CONVERT(datetime,substring(ltrim(rtrim(substring(InLine, 40, len(InLine)))),LEN(ltrim(rtrim(substring(InLine, 40, len(InLine)))))-17,8)),getdate())>@days_old_to_allow --Delete the files-- OPEN c_files FETCH NEXT FROM c_files INTO @mtime, @file, @daysold WHILE(@@fetch_status = 0) BEGIN SET @fullpath = @basedir + '\' + @file PRINT 'Going to delete old file: ' + @fullpath + ', daysold=' + cast(@daysold as nvarchar) SET @cmd = 'del /Q "' + @fullpath + '"' print @cmd -- no turning back now! EXEC master.dbo.xp_cmdshell @cmd, no_output FETCH NEXT FROM c_files INTO @mtime, @file, @daysold END CLOSE c_files DEALLOCATE c_files DROP TABLE #t_dirEND
Wow, you saved me thanks!
January 19, 2018 at 10:43 am
larry Hennig - Thursday, February 5, 2009 9:43 AMThe maintenance task calls the same SP, so that is why the SP call makes no difference. The bug that prevents the delete from working is fixed in a post-SP2 cumulative update (CU 2 or higher, I think, it's in CU 10 for sure). Prior to the fix, believe it or not, the procedure read the first line of the file. If that line was "NEW COMPONENT OUTPUT", the file was not deleted and no warning or error was raised. The update removes this constraint.Hopefully, they fired the idiot who designed the original behavior.
They can't afford to as there would be no one left on the dev team.
January 19, 2018 at 4:37 pm
larry Hennig - Friday, February 6, 2009 12:33 PMCmdShell is a huge security hole. Does anyone have an SSIS package or a .Net source code for a DLL that could be called to do this without enabling xp_CmdShell?
No Sir. xp_CmdShell isn't a security hole, never mind a huge one. The only people that can use it are the same people that can enable it. If an attacker get's in with any privs other than sysadmin privs, the attacker won't be able to use xp_CmdShell. If they get in with sysadmin privs, they can do absolutely anything they want and you having xp_CmdShell turned off will only provide a 3ms speed bump for their attack software.
xp_CmdShell is a powerful tool that you're depriving yourself of because of old wives' tales from a pre-2005 era. Concentrate on keeping the bad guys out rather than the perpetual myth of xp_CmdShell being a security hole.
Heh... and let's stop and think about your request to do it in SSIS... that's an expansion of the very surface area that you're concerned with.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2018 at 8:10 am
Post is 9 years old, surprised it has resurfaced, Jeff you are right. Not sure about the perception back then, seem to recall certain warnings or is my memory fading?
...
January 20, 2018 at 1:38 pm
HappyGeek - Saturday, January 20, 2018 8:10 AMPost is 9 years old, surprised it has resurfaced, Jeff you are right. Not sure about the perception back then, seem to recall certain warnings or is my memory fading?
Agreed on the age of the post but someone necro'd it and the post that "saved" the recent poster involved the use of xp_CmdShell. Some threads are just timeless so I started looking back through the thread to see what happened 9 years ago. Sure enough, there's a post about the "security" of xp_CmdShell. Other people may do the same as I did and read through this post and since that one post could negate the whole value of the solution that "saved" the latest poster because of an uniformed poster repeating the myth contained in an old wives' tale and I don't want a 9 year old thread to perpetuate an untrue myth, I took the time to post about xp_CmdShell not being a security issue to help try to stop the myth in the eyes of anyone that reads the thread.
To answer you latter question, yes... it was more difficult to use xp_CmdShell in a highly secure fashion before SQL Server 2005 and that is the source of the old wives' tale (they all have some basis in fact even if the facts are no longer applicable) about security issues with xp_CmdShell. But they've carried forward over the last 15 years just due to the shear volume of "experts" and their entourage of nodders that haven't done (apparently) any research since then and the myth continues to grow through the say-so of more nodders that are trying to look intelligent on the subject.
I'm just one person and it's not likely that I can single-handedly stem the tide of seriously abusive disinformation about xp_CmdShell but I have to try. I have done a presentation on the subject many times. Maybe it's finally time for me to convert that presentation into an article.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply