February 3, 2009 at 6:07 pm
When a maintenance plan is run it drops a .txt file in the directory. The clean up maintenance plan is set to delete this files after a week but it doesn't work so I wrote this script to delete them. It says it runs successfully but nothing actually happens.
Can you please tell me what's wrong with the script?
DECLARE @cmd nvarchar(2000)
SET @cmd = 'EXEC master.dbo.xp_delete_file 0,N''\\ServerName\C$\SQL Data\MSSQL.1\MSSQL\LOG'',
N''txt'',N''' + CONVERT(nvarchar(256),Dateadd (dd, -6, getdate()), 101) + ''''EXEC (@cmd)
Kris
February 4, 2009 at 12:02 am
You query will delete all the files with 'txt' extension.
But errorlog files don't have any extension. It's sumthing like...
ERRORLOG (Current log file)
ERRORLOG.1
ERRORLOG.2
ERRORLOG.3
.........
So 'xp_delete_file' will run successfully without deleting anything.
Hope this helps.
February 4, 2009 at 12:45 am
Are you using SQL Server 2000 or 2005?
Rajesh Kasturi
February 4, 2009 at 5:29 am
can you try following code:
-----------------------
DECLARE @cmd nvarchar(2000)
SET @cmd = 'EXEC master.dbo.xp_delete_file 0,N''\\ServerName\C$\SQL Data\MSSQL.1\MSSQL\LOG\'',
N''txt'',N''' + CONVERT(nvarchar(256),Dateadd (dd, -6, getdate()), 101) + ''''EXEC (@cmd)
---------------------------
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 4, 2009 at 5:45 am
master..xp_cmdshell 'del dir \\.....................BKP\*.bak' try this one
February 4, 2009 at 8:10 am
Hi,
This is a known bug that is fixed with SP1.
Find the below URL for more info....
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/5924074f-5f1c-444a-bd09-fc3e999f2637/
and
http://www.sqlservercentral.com/Forums/Topic359308-338-1.aspx#bm361850
Rajesh Kasturi
February 4, 2009 at 4:56 pm
I'm not trying to delete the error logs, I'm trying to delete all file with the extension of txt. It's SQL 2005 SP2. I tried the script and same thing. It says it's successful but nothing actually happens.
Kris
February 5, 2009 at 1:04 am
Hi,
The user that runs the SQL Agent has permissions on the directory?
February 5, 2009 at 9:43 am
The 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.
February 5, 2009 at 5:40 pm
yes. Permissions are not an issue. I'm running the script as sys admin.
Kris
February 6, 2009 at 11:40 am
Kris, This is covered somewhere else, but I'll post it here because I don't have a link. Credit goes to someone else.
Set up a job using an ActiveXScript. Change the path and number of days of retention to fit your environment:
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
Beth Richards
Sybase, Oracle and MSSQL DBA
February 6, 2009 at 12:05 pm
I 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\', 30
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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.***
AS
BEGIN
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_dir
END
February 6, 2009 at 12:33 pm
CmdShell 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?
February 6, 2009 at 1:26 pm
Larry, agree with u on that.
So i tried bethrich's method: I put the scirpts in bethrich's post into a job
and job was executed successfully, but the files in my test folder are still there: although they are txt file and older than 14 days.
Bethrich, how did you implement the script in your job? could u please share with us?
Thanks!
February 8, 2009 at 4:47 am
Kris (2/3/2009)
When a maintenance plan is run it drops a .txt file in the directory. The clean up maintenance plan is set to delete this files after a week but it doesn't work so I wrote this script to delete them. It says it runs successfully but nothing actually happens.Can you please tell me what's wrong with the script?
DECLARE @cmd nvarchar(2000)
SET @cmd = 'EXEC master.dbo.xp_delete_file 0,N''\\ServerName\C$\SQL Data\MSSQL.1\MSSQL\LOG'',
N''txt'',N''' + CONVERT(nvarchar(256),Dateadd (dd, -6, getdate()), 101) + ''''EXEC (@cmd)
Scripts are wonderful, but my maintenance plan (SQL2005 SP2) deletes the logs older than 2 weeks as I asked it within the plan. The only time it did not was when it was pointing to the backup folder instead of the log folder...
Perhaps you should check that the maint plan points to the correct folder where the logs are created (use the ellipsis button). The plan runs under Windows credentials - does the user have full control to the logs folder? When something is supposed to work, let's make it so.
HTH
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply