/*============================================================================
  File:     6_Baselines_Cleanup.sql

  Summary:  This script contains the statements to
			regularly query the captured cache usage data.

  Date:     April 2017

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  (c) 2015, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE [BaselineData];
GO

/*
	sample of ad-hoc statement that can be used
	to remove historical data
*/
DELETE  FROM [dbo].[SQLskills_WaitStats]
WHERE   [CaptureDate] < GETDATE() - 90;


/*
	stored procedure that can be used to 
	remove historical data
*/
IF OBJECTPROPERTY(OBJECT_ID(N'usp_PurgeOldData'), 'IsProcedure') = 1 
    DROP PROCEDURE usp_PurgeOldData;
GO

CREATE PROCEDURE dbo.usp_PurgeOldData
    (
      @PurgeConfig SMALLINT ,
      @PurgeFile SMALLINT ,
      @PurgeWaits SMALLINT ,
	  @PurgeCache SMALLINT     
    )
AS 
    BEGIN;
        IF @PurgeConfig IS NULL
            OR @PurgeCounters IS NULL 
            BEGIN;
                RAISERROR(N'Input parameters cannot be NULL', 16, 1);
                RETURN;
            END;

        DELETE  FROM [dbo].[SQLskills_ConfigData]
        WHERE   [CaptureDate] < GETDATE() - @PurgeConfig;

        DELETE  FROM [dbo].[SQLskills_FileInfo]
        WHERE   [CaptureDate] < GETDATE() - @PurgeFile;

        DELETE  FROM [dbo].[SQLskills_WaitStats]
        WHERE   [CaptureDate] < GETDATE() - @PurgeWaits;

		DELETE  FROM [dbo].[SQLskills_CacheUseByDB]
        WHERE   [CaptureDate] < GETDATE() - @PurgeCache;

    END;


/*
	Example of how to execute SP
*/
EXEC dbo.usp_PurgeOldData 120, 60, 60, 30