Hi,
Our K2Log database is very large and can cause performance issues on the server when we try and back up the disk. As a result of this, I have been tasked with finding a way to reduce the size of the database and keep it under control in future.
I have found that K2.net 2003 SP3 (which is what we are running) offers an archiving tool, which I have tested and confirmed that it provides the result I want.
While browsing the net for this, I came across a blog which contained this script for automating the process:
Set the old archive start date
Set @maxPreviousArchiveStartDate=(Select max(FromDate) From _ArchiveTo)
– Set the archive start date
Set @archiveStartDate = @maxPreviousArchiveStartDate
Set @archiveStartDate = DateAdd(month, 1, @archiveStartDate) — add one month
Set @archiveStartDate = Cast(DatePart(dd, @archiveStartDate) as varchar) + ‘/’
+ Cast(DatePart(mm, @archiveStartDate) as varchar) + ‘/’
+ Cast(DatePart(yyyy, @archiveStartDate) as varchar)
– Set the archive end date
Set @archiveEndDate = @maxPreviousArchiveStartDate
Set @archiveEndDate = DateAdd(month, 2, @archiveEndDate)
Set @archiveEndDate = DateAdd(day,-1, @archiveEndDate)
Set @archiveEndDate = Cast(DatePart(dd, @archiveEndDate) as varchar) + ‘/’
+ Cast(DatePart(mm, @archiveEndDate) as varchar) + ‘/’
+ Cast(DatePart(yyyy, @archiveEndDate) as varchar)
Set @command = ‘”‘ + (Select ApplicationValue From AppDB..ApplicationControl
Where ApplicationGroup=’K2 Archive’ And ApplicationKey=’K2 Archive Creation’) + ‘”‘ + ‘ [’ + @archiveStartDate + ‘] [’ + @archiveEndDate + ‘]’
Exec master..xp_cmdshell @command
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘K2 Archiving’,
@recipients = ‘k2admin@abc.com’,
@subject = ‘K2 Archiving Notification’,
@body = ‘K2 Archiving job is completed.’
If you ignore the slightly dodgy parts of syntax and invalid characters I was wondering if anyone could help me get this to work. The bit I'm particularly struggling to understand is the line that starts 'Set @command' What is this doing? What is it for?
Another thought that crossed my mind was that there may be some system specific names in there which I wasn't aware of, if anyone spots any of these then please point them out and let me know, in generic terms, what they should be.
Any help with automating the process not related to this script is also very welcome.
TIA
M
