CA Service Management

We have created a rule named notify logs in archive and purge rule. When this rule runs it creates it logs history but the issue is that it is consuming so much space that attachments issue is happening. Notify logs is consuming 56 GB of memory size. What is the purpose of this rule ? Can we delete this rule so that space issue is resolved and is there any effect on other services by deleting this rule??

Following is the path where this data is placed.

C:\Program Files (x86)\CA\Service Desk Manager\site\data\archive

File name: *********_Notify_Logs

Strange coincidence that we get two queries on the same topic so quickly - are you and Amer Khan at the same site?  Anyway, I would suggest you set the rule to purge Notify Logs, not archive them.  The notify log typically accumulates a large number of records very quickly and there is little value in keeping them archived.

Regards,

James

Yes we are..

We have defined its purge only but it is creating logs ..

What we do for logs that are already created?

Can we delete them?

Is there any effect if we delete the logs because they are consuming so much of our space.

Regards,

I and Ahmer Ali khan both are working on the same site. This issue is very critical and of high priority. We both are working on this so that we resolve this issue as early as possible.

Your usual support is required in this case.

Basically we want to retain only 3 years data. But we have more than 3 years data. We want to delete all other data than last 3 years. Therefore we make this notify log rule so that it could retain only 3 years data..

But it’s not working fine, it’s not deleting any data..

The option which you told about only purge whether it will work fine? I will do that but would it meet my requirement of retaining 3 years data ??

Also this notify log rule cause waste of memory and if it is not the rule for this issue then can we delete/inactive this rule so that we free our memory from consumption/.

Kindly do needful action in this regard.. We are in a great trouble for this issue…

If you only need to retain 3 years of notify logs then you might like to consider deleting the records older than 3 years using SQL rather than wait for archive / purge to do it record by record.  However, this is not necessarily an easy operation given that you may have a very large number of records, and requires some preparation.  Consult your DBA, if you have one available on site.

• Ensure you have a backup of the MDB database. 
• Run SQL to count the number of records to be deleted and to be retained.  Identify the threshold for deletion in terms of the record id.  Do the delete using 'where id between mmmmm and nnnnnnnn' rather than 'where last_mod between xxxxxxx and yyyyyyy', as the column 'id' is indexed but 'last_mod' is not.
• Consider deleting the unwanted not_log records in batches of several thousand rather than trying to do all at once.  You could easily have several million records to delete, and deleting in smaller batches will give you more control over the process.
• If your MDB is running under 'Full' recovery mode, when you run the SQL delete the SQL transaction log may grow unacceptably large due to the volume of deleted records.  Talk to your DBA about how to avoid this.  One approach might be to stop SDM services in a maintenance window, take a full backup of the MDB, change the database recovery mode to 'Simple', take another backup, run the SQL delete, then revert to 'Full' recovery mode and take another full backup before starting the SDM services again.

Hope that helps...

Regards,

James

Here's an example stored procedure I found and modified to delete stakeholders in batches of 10000.  We pragmatically add stakeholders to incidents for outage subscription notifications.  To keep the table clean I purge the records that are older than 30 days.

You can modify this SQL to create a procedure for not_log, just change the table name, stored procedure name, and where clause.  Once you've created the SP it can be executed via SQL, 'EXEC <stored procedure name>'.

USE [mdb]

GO



/****** Object:  StoredProcedure [dbo].[zPurge_Stakeholders]    Script Date: 5/10/2018 3:55:51 PM ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



-- =============================================

-- Author:           Grant Bruneau

-- Create date: 08/23/2017

-- Description:      Delete Stakeholders from Service Interruption where the close date is more than one month ago

-- =============================================

CREATE PROCEDURE [dbo].[zPurge_Stakeholders]

       -- Add the parameters for the stored procedure here

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

              SET NOCOUNT ON;

              DECLARE @r INT;

              SET @r = 1;

              WHILE @r > 0

              BEGIN

                BEGIN TRANSACTION;


                DELETE TOP (10000) -- Number of rows to delete in one batch

                     dbo.usp_lrel_notify_list_cntntf --table name goes here

                           WHERE id IN(SELECT LREL.id

                           FROM usp_lrel_notify_list_cntntf LREL

                           INNER JOIN call_req CR

                           ON LREL.cr = CR.persiD

                           WHERE CR.close_date < (DATEDIFF(s, '1970-01-01 00:00:00', GETDATE()) - (30 * 24 * 60 ^ 60))) -- Change the first integer to increase/decrease the days to purge


                SET @r = @@ROWCOUNT;

                COMMIT TRANSACTION;

                --CHECKPOINT;    -- if simple

                -- BACKUP LOG ... -- if full

              END

              ALTER INDEX ALL ON dbo.usp_lrel_notify_list_cntntf

              REORGANIZE;

END


GO

Bakhtawar.......

Did any of the information provided answer your question regarding purging the SDM Notification MDB table?

If so, please mark one of the answers as correct so that this thread can be closed.