Delete records older than 6 months sql table

With out knowing what is in your stored procedure it is hard to give advice. even then your deleting data from a DB so caution should be taken.

In general I would start by doing a select using the same criteria as your passing the stored procedure to identify the records/number that will be deleted.

if the count is over your limit then start with a older date.

beyond that I don't know what advice to give.

Regards,

Yes, evidently your SP has some check constraint in it to limit the records it deletes at a time.  
Does it error out and not delete any records or does it stop deleting at that limit?
If it errors out, you will have to delete a smaller batch at a time, i.e start with a smaller # days and keep running it till you get to the 180 days.
If it does delete records but stops at the limit, you just need to keep running it till all the records are gone.
HTH,
Chris

This is the SP:--

USE [BarTender]
GO

/****** Object:  StoredProcedure [dbo].[SpDeleteRecords]    Script Date: 7/27/2017 9:48:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*---------------------------------------------------------------------------*/
/* Procedure:   SpDeleteRecords                                              */
/* Purpose:     Remove Records older than pastUtcTicks within "categories"   */
/*              list                                                         */
/* Params:      @pastUtcTicks - Cutoff Date in Ticks                         */
/*              @categories  - Record Categories                             */
/*---------------------------------------------------------------------------*/
CREATE PROC [dbo].[SpDeleteRecords](@pastUtcTicks bigint, @categories nvarchar(1024))
AS
/*---------------------------------------------------------------------*/
/* Configure the selected items to clean up, based on @categories      */
/* @categories contains the bit positions of selected table categories */
/*---------------------------------------------------------------------*/
DECLARE @btPrintJobBit int, @btLabelDataBit int, @btLabelImageBit int
DECLARE @btMessageBit  int
DECLARE @btFormatsBit  int, @btFormatBlobsBit int
DECLARE @appMessageBit int
DECLARE @pmPrinterEventBit    int, @pmInvEventBit int, @allBit int
DECLARE @scEventsBit   int
DECLARE @selectionBits int, @delim nvarchar(20)
DECLARE @minIdentLimit int
DECLARE @pastUtc       datetime

SET @btMessageBit       = [dbo].[udfGetPurgeCategoryBit](N'BTMS')
SET @btLabelImageBit    = [dbo].[udfGetPurgeCategoryBit](N'BTLI')
SET @btLabelDataBit     = [dbo].[udfGetPurgeCategoryBit](N'BTLD')
SET @btPrintJobBit      = [dbo].[udfGetPurgeCategoryBit](N'BTPJ')
SET @btFormatBlobsBit   = [dbo].[udfGetPurgeCategoryBit](N'BTFB')
SET @btFormatsBit       = [dbo].[udfGetPurgeCategoryBit](N'BTFM')
SET @appMessageBit      = [dbo].[udfGetPurgeCategoryBit](N'APMS')
SET @pmPrinterEventBit  = [dbo].[udfGetPurgeCategoryBit](N'PMPR')
SET @pmInvEventBit      = [dbo].[udfGetPurgeCategoryBit](N'PMIN')
SET @scEventsBit        = [dbo].[udfGetPurgeCategoryBit](N'SCEV')
SET @allBit             = [dbo].[udfGetPurgeCategoryBit](N'ALLR')

SET @selectionBits = 0
SET @delim = N';'
SET @minIdentLimit = 50000000 /* 50 Million */

EXEC [dbo].[SpGetSelectionBits] @categories, @delim, @selectionBits output

IF @selectionBits = 0 RETURN 0
IF @pastUtcTicks = 0 RETURN 0
SET @pastUtc = [dbo].[udfTicksToDateTime](@pastUtcTicks)

BEGIN TRANSACTION
/*-----------------------------------*/
/* BtPrintJobs and subordinates      */
/*    BtPrintedLabels                */
/*       BtObjects & Substrings      */
/*       BtPrintedLabelImages        */
/*    BtPrintJobMessages             */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @btPrintJobBit) > 0))
BEGIN
/* Label Data and Images */
EXEC [dbo].[SpPurgeOldBtRecords] N'BtPrintedLabels', N'PrintJobID', N'PrintJobID', @pastUtcTicks
EXEC [dbo].[SpPurgeOrphanedLabelTemplates]
EXEC [dbo].[SpPurgeOldImages] 5

/* Fix the Identity Seeds */
EXEC [dbo].[SpReseedTable] N'BtPrintedLabels', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'BtObjects', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'BtSubStrings', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'BtImageBlobs', @minIdentLimit

/* Print Jobs */
DELETE FROM [dbo].[BtPrintJobs] WHERE CreatedUTC < @pastUtcTicks

/* Fix the Identity Seeds */
EXEC [dbo].[SpReseedTable] N'BtPrintJobs', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'BtPrintJobMessages', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'BtJobStatus', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'BtPrintJobOptions', @minIdentLimit

/* Print Job Command Lines */
EXEC [dbo].[SpPurgeOrphanedRecords] N'BtPrintJobCommandLines', N'CommandLineID', N'BtPrintJobs', N'CommandLineID'
EXEC [dbo].[SpReseedTable] N'BtPrintJobCommandLines', @minIdentLimit
END
ELSE IF ([dbo].[udfTestBit](@selectionBits, @btLabelDataBit) > 0)
BEGIN
/* Label Data */
EXEC [dbo].[SpPurgeOldBtRecords] N'BtPrintedLabels', N'PrintJobID', N'PrintJobID', @pastUtcTicks
EXEC [dbo].[SpPurgeOrphanedLabelTemplates]
END
ELSE IF ([dbo].[udfTestBit](@selectionBits, @btLabelImageBit) > 0)
BEGIN
/* Label Images only */
EXEC [dbo].[SpPurgeOldLabelImages] @pastUtcTicks

/* Fix the Identity Seeds */
EXEC [dbo].[SpReseedTable] N'BtPrintedLabelImages', @minIdentLimit
END

/*-----------------------------------*/
/* BtPrintJobMessages                */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) = 0) AND
([dbo].[udfTestBit](@selectionBits, @btPrintJobBit) = 0) AND
([dbo].[udfTestBit](@selectionBits, @btMessageBit) > 0))
BEGIN
EXEC [dbo].[SpPurgeOldBtRecords] N'BtPrintJobMessages', N'PrintJobID', N'PrintJobID', @pastUtcTicks
EXEC [dbo].[SpReseedTable] N'BtPrintJobMessages', @minIdentLimit
END

/*-----------------------------------*/
/* BtFormats & BtFormatBlobs         */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @btFormatsBit)) > 0)
BEGIN
EXEC [dbo].[SpPurgeOrphanedRecords] N'BtFormats', N'FormatID', N'BtPrintJobs', N'FormatID'
EXEC [dbo].[SpPurgeOrphanedFormatBlobs]
EXEC [dbo].[SpReseedTable] N'BtFormatBlobs', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'BtFormats', @minIdentLimit
END
ELSE IF ([dbo].[udfTestBit](@selectionBits, @btFormatBlobsBit) > 0)
BEGIN
/* BtFormatBlobs on its own */
EXEC [dbo].[SpPurgeOrphanedFormatBlobs]
EXEC [dbo].[SpReseedTable] N'BtFormatBlobs', @minIdentLimit
END

/*-----------------------------------*/
/* Application Messages              */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @appMessageBit) > 0))
BEGIN
DELETE FROM [dbo].[MessageLog] WHERE [dbo].[MessageLog].[UTC] < @pastUtcTicks
EXEC [dbo].[SpReseedTable] N'MessageLog', @minIdentLimit
END

/*-----------------------------------*/
/* Maestro Printer events            */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @pmPrinterEventBit) > 0))
BEGIN
EXEC [dbo].[SpPurgeOldPmPrintJobs] @pastUtcTicks
EXEC [dbo].[SpReseedTable] N'PmPrinterEvents', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'PmPrintJob', @minIdentLimit
END

/*-----------------------------------*/
/* Maestro Inventory events            */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @pmInvEventBit) > 0))
BEGIN
EXEC [dbo].[SpPurgeOldPmInvEvents] @pastUtcTicks
EXEC [dbo].[SpReseedTable] N'PmInvEvents', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'PmInvItems', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'PmInvItemAttributes', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'PmInvEventAttributes', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'PmInvAttributeStrings', @minIdentLimit
END

/*-----------------------------------*/
/* Maestro Orphaned strings          */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @pmPrinterEventBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @pmInvEventBit) > 0))
BEGIN
EXEC SpPurgeOrphanedPmStrings
EXEC [dbo].[SpReseedTable] N'PmInvStrings', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'PmInvItemStrings', @minIdentLimit
EXEC [dbo].[SpReseedTable] N'PmStrings', @minIdentLimit
END

/*-----------------------------------*/
/* Security events                   */
/*-----------------------------------*/
IF (([dbo].[udfTestBit](@selectionBits, @allBit) > 0) OR
([dbo].[udfTestBit](@selectionBits, @scEventsBit) > 0))
BEGIN
EXEC [dbo].[SpPurgeOldSecurityEvents] @pastUtcTicks
EXEC [dbo].[SpReseedTable] N'ScSecurityEvents', @minIdentLimit
END

/*-----------------------------------*/
/* Defragment Indexes                */
/*-----------------------------------*/
DECLARE @sqlMajorVer decimal
SET @sqlMajorVer = [dbo].[udfGetSqlMajorVer]()

/* SpDefragmentIndexes can't run in a Transaction if SQL Server is */
/* < 2008 */
IF (@sqlMajorVer < 10)
BEGIN
/* LOGDBWIZARD: IGNORE */
IF @@ERROR <> 0 ROLLBACK ELSE COMMIT
END

EXEC [dbo].[SpDefragmentIndexes] '20.0', '50.0'
EXEC sp_updatestats

IF (@sqlMajorVer > 9)
BEGIN
/* LOGDBWIZARD: IGNORE */
IF @@ERROR <> 0 ROLLBACK ELSE COMMIT
END

RETURN @@ROWCOUNT

GO

The db creation date is 6/18/2015. There are huge amount of data. I set this 540 days but still log file is growing. In the sp there is SET @minIdentLimit = 50000000 /* 50 Million */  so should I minimize this limit?

Your log file is going to grow, because you are doing deletes against the database.  It is "logging" those deletes.  What problem is the log file growth causing?
What is your SpReseedTable, that is where the @minIdentLimit is being used?  But if it is just resetting the identity, changing that probably does not change anything related to the log file growth.

And you still have not shared if you are getting an error somewhere or just concerned with the growth?

This is basically bartender system database http://help.seagullscientific.com/2016/en/Subsystems/AdminConsole/Content/Remote_Maintenance.htm

I am not getting an error, only just seeing log file is growing due to delete operation.

OK, I looked at that link and it looks like you are doing what it says.  There is not really anything you could or should do about the log file growth, that is normal database behavior.

I understand but should I increase the log drive to perform this task. Even I minimize the limit but didn't work,  I need to accomplish this task soon. Do you have any suggestion. Please advise.

I  am doing testing in my local machine now the log size is 102 GB now. Even I set the days limit 540 instead of 180 days

If the drive is close to full, then you probably need to increase the size.  You can not need as much room if you do it in smaller batches.  You say the database started on '6/18/2015' which is 770 days ago, if you set your days to say 750, does it work and not grow so much?

Or all the way down to 1 day at a time, start with 770 and decrease to 180.  Not sure how efficient the routine is or how long one pass takes.

"if you set your days to say 750, does it work and not grow so much? "

That was my mistake. I have stop the job now. and going to set 540 days. One thing what about this value SET @minIdentLimit = 50000000 /* 50 Million */

What should I put this value for SET @minIdentLimit =?

From what I could make out from the code you shared, that looks like what it uses to reset the Identity Field on tables and will not affect the # rows deleted.  Without the entire code base, I can not provide better details.

Yes, this need to be set up as maintenance plan.

I will appreciate your help

i have completed this.

Inactive