none
Huge DPM database - tbl_TE_TaskTrail RRS feed

  • Question

  • DPM 2007 SP1

    We recently received some disk space alerts on our DPM server. Our DPM database is up to 50GB and is on a physical server/volume (i.e. we cannot add space). After looking through some of the tables the tbl_TE_TaskTrail table is 27GB in size. From what I have read there should be a "garbage collection" task that purges old records from this table, however that does not seem to be working. We have a separate DPM server that we use for testing and the TE_TaskTrail table in that database makes up less than 3% of the database size.

    Is there any way to clear this up? I tried running the script in this thread: http://social.technet.microsoft.com/Forums/ta/dataprotectionmanager/thread/fc7989fc-7b9d-41dc-b07b-62c97e7de1b1 and modifying it to delete records from the TaskTrail table but it did not work.

    Thank you.

    Wednesday, September 12, 2012 3:12 PM

All replies

  • Thank you for the reply.

    I started the ""97AB8BC2-B697-48db-AD1F-9850F1163B7E" job and it only took about 2 seconds to run, I am guessing it did not run properly. Is there a way to tell? The table did not go down in size.

    Wednesday, September 12, 2012 4:08 PM
  • Hi 

    You can run the command in the link to shrink the DB. Do you have the latest QFE installed?

    You can run SQL DBCC command to shrink the DPM database size
    (http://msdn.microsoft.com/en-us/library/ms190488.aspx

    Wednesday, September 12, 2012 4:26 PM
  • The "space available" for that database is only listed at 1.3GB, does that mean that is the maximum amount of space I could reclaim by performing the DBCC SHRINKDATABASE? I apologize for my lack of knowledge, our DBA was fired due to some recent downsizing, I'm guessing he would have fixed this in about 5 minutes.
    Wednesday, September 12, 2012 5:07 PM
  • Hi

    no problem sorry for the late reply :-)

    Have a look at the advise regarding running this command:

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/691218c8-c2c7-498e-92d6-eb61f0a1ba63

    Thursday, September 13, 2012 9:59 AM
  • Well I started up the shrinkdatabase command last night before I left and this morning it was completed, however now we only have about 120MB free on the volume (vs. the 10MB free previously), meaning it only "shrank" by about 110MB. Any clue on how to reduce the size of that TaskTrail table?
    Thursday, September 13, 2012 12:03 PM
  • Hi EJ72,

    Did you take a look at this thread ? http://social.technet.microsoft.com/Forums/en-US/dataprotectionmanager/thread/e9cc1789-04f0-4b8d-b92f-8149c1b49d95

    It seem's to be the same issue as yours.

    Hope this help you.

    Stephane


    Please remember to click “Mark as Answer” on the post that helps you. This posting is provided "AS IS" with no warranties. knowledge is valid only if it is shared by All.

    Thursday, September 13, 2012 12:57 PM
  • How do I "manually ran the prc_TE_GarbageCollect sproc in 1 week increments"?
    Thursday, September 13, 2012 1:36 PM
  • Hi EJ72,

    Please note that this answer is provided "AS IS" with no warranties. (In other words don't do that ;) Maybe it's not supported by MS)

    Please make a backup of your DPM DB before to progress.

    You may open Microsoft SQL Server Management Studio :

      • Select your DPMDB and expand it
      • Go to "Stored Procedures" Folder and find "prc_TE_GarbageCollect sproc"
      • Right-click / Modify
      • Find the "30 days" settings and decrease to lower
      • Launch "prc_TE_GarbageCollect sproc" manually

    tbl_TE_TAskTrail is the history of all DPM jobs that ran or failed.  It is used to populate the JOBS tab.  DPM maintain 30 days worth of job history and it's pruned nightly to delete jobs history older than 30 days.

    Stephane


    Please remember to click “Mark as Answer” on the post that helps you. This posting is provided "AS IS" with no warranties. knowledge is valid only if it is shared by All.

    Thursday, September 13, 2012 2:24 PM
  • Hi I agree with Stephane, make a backup first before changing anything. Stephane, i am sure that a SQL script can be created for this that can be executed at leisure?
    Thursday, September 13, 2012 3:33 PM
  • Thank you for everyone's continued assistance with this. I am not sure why, but that stored procedure on our DPM server does not contain a reference to "30 days":

    USE [DPMDB]
    GO
    /****** Object:  StoredProcedure [dbo].[prc_TE_GarbageCollect]    Script Date: 09/13/2012 12:30:22 ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

    ALTER PROCEDURE [dbo].[prc_TE_GarbageCollect]
    (
        @GCTill datetime
    )
    AS

    DECLARE @error int
    SET NOCOUNT ON

    SET @error = 0

    DELETE
    FROM dbo.tbl_TE_TaskTrail
    WHERE StoppedDateTime < @GCTill
    AND dbo.tbl_TE_TaskTrail.TaskId NOT IN
            (SELECT taskId FROM tbl_AM_AgentTask_Alerts)
    AND dbo.tbl_TE_TaskTrail.TaskId NOT IN
            (SELECT taskId FROM tbl_MM_MediaRequiredAlert)

    SELECT @error = @@ERROR

    SET NOCOUNT OFF

    RETURN @error

    Thursday, September 13, 2012 5:30 PM
  • Hi EJ72,

    The time variable is: @GCTill datetime

    A DBA advice should be interesting :)

    You may try to run this stored procedure without modification and see the result.

    Stephane


    Please remember to click “Mark as Answer” on the post that helps you. This posting is provided "AS IS" with no warranties. knowledge is valid only if it is shared by All.

    Friday, September 14, 2012 7:05 AM
  • Hi DareDevil57,

    You're right :) Something like this post : http://social.technet.microsoft.com/Forums/en-US/dataprotectionmanager/thread/fc7989fc-7b9d-41dc-b07b-62c97e7de1b1

    But I don't know how to do that :)

    Stephane


    Please remember to click “Mark as Answer” on the post that helps you. This posting is provided "AS IS" with no warranties. knowledge is valid only if it is shared by All.

    Friday, September 14, 2012 7:06 AM
  • Hi Stephane

    In IT we need to work smart, not hard :-)

    If you copy the script as per that link and execute it, it normally asks if you want to save the script if you click on the X button. :-)


    Friday, September 14, 2012 7:10 AM
  • Hi Daredevil57,

    I know that :)

    I was thinking how to modify this SQL script with good DPM values ^^

    Stephane


    Please remember to click “Mark as Answer” on the post that helps you. This posting is provided "AS IS" with no warranties. knowledge is valid only if it is shared by All.

    Friday, September 14, 2012 8:36 AM
  • This is what I get if I try to run that stored procedure as is:

    Msg 547, Level 16, State 0, Procedure prc_TE_GarbageCollect, Line 13
    The DELETE statement conflicted with the REFERENCE constraint "fk_RM_ShadowCopyTrail__TE_Task". The conflict occurred in database "DPMDB", table "dbo.tbl_RM_ShadowCopyTrail", column 'TaskId'.
    The statement has been terminated.

    (1 row(s) affected)

    Friday, September 14, 2012 12:06 PM