Microsoft KB Archive/331071

= How To Delete Log Import Data by Using a SQL Script =

Article ID: 331071

Article Last Modified on 7/1/2004

-

APPLIES TO


 * Microsoft Site Server 3.0 Standard Edition

-



This article was previously published under Q331071



SUMMARY
This article contains a script that you can use to delete log import data before a specified date.



MORE INFORMATION
When you use Site Server Analysis with Microsoft SQL Server, and you perform a delete operation by using Import History Manager in Usage Import, indexes are created before the delete operation occurs. This is intended to improve performance. However, it may take a long time to create indexes if you have a large database. You can use the following SQL script instead of Import History Manager to delete log import data before a specified date: -- The script helps you delete log import data. -- You need to specify the date (YYYY-MM-DD). -- All imports prior to that date will be purged. Declare @DateToPurge smalldatetime Select @DateToPurge = '2002-01-06'

Select Dateid into #TempDateID from tblImportDate where vTimeStamp < @DateToPurge Select vTimeStamp into #TempTimeStamp from tblImportDate where vTimeStamp < @DateToPurge Select ImportID into #TempImportID from tblImportSummary where DateID in (select Dateid from #TempDateID)

BEGIN TRAN Delete tblImportMonth where vTimeStamp in (select vTimeStamp from #TempTimeStamp) Delete tblImportQuarter where vTimeStamp in (select vTimeStamp from #TempTimeStamp) Delete tblImportYear where vTimeStamp in (select vTimeStamp from #TempTimeStamp) Delete tblInterseIP where vTimeStamp in (select vTimeStamp from #TempTimeStamp)

Delete tblIntersehistory where ImportID in (select ImportID from #TempImportID) Delete tblIntersehistoryDetail where ImportID in (select ImportID from #TempImportID)

Delete tblImportVisit where DateID in (select DateID from #TempDateID) Delete tblImportRequest where DateID in (select DateID from #TempDateID) Delete tblImportADRequest where DateID in (select DateID from #TempDateID) Delete tblImportSummary where DateID in (select Dateid from #TempDateID) Delete tblImportDate where vTimeStamp < @DateToPurge

IF @@ERROR <>0 BEGIN

ROLLBACK TRAN

PRINT 'Error! Now rolling back transactions' END ELSE COMMIT TRAN

--select * from tblimportDate where vTimeStamp < @DateToPurge

go Drop Table #tempdateid go Drop Table #tempTimeStamp go Drop Table #tempImportID go

Keywords: kbhowto KB331071

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.