none
DPMDB Database and Log File DPM 2012 get very big with lot's of Free Space in just one day

    Question

  • Hi,

    Since around a week my DPMDB and log file gets so big i run out of space on my local hdd. I use a local sql instance 2008 r2 sp1. I run DPM 2012.

    This morning the DB was 25GB with 76% Free Space. The Log was 45GB with 99% Free Space.

    Only thing I can do is shrink both. But I can only do this after deleteing other stuff to make some room. So it's not a good situation.

    Edit - It seems it has something to do with the SharePoint Catalog Task - Edit

    I browsed a hyper-v server to recover a file some days ago. Can this be the cause?

    I update to Rollup 3 a bit more then a week ago. Can this be the cause?

    Anyone know of this?

    Anyone has an idea how i can troubleshoot this?

    All help appreciated.

    DJITS.


    • Edited by DJITS Sunday, October 28, 2012 9:33 AM Adition
    Saturday, October 27, 2012 9:43 AM

Answers

  • I faced the same issue after Update Rollup 3 and we raised a case with Microsoft and the Esclation team were able to fix it. I believe it will be reported as a bug and hopefully get fixed in the SP1

    For that we modified store procedure prc_PRM_SharePointRecoverableObject_Update. (You can find it under the DPMDB - Programability - Stored Procedures), Just take a DPM backup before any change for your reference.

    From:

    USE [DPMDB]

    GO

    /****** Object: StoredProcedure [dbo].[prc_PRM_SharePointRecoverableObject_Update] Script Date: 11/02/2012 17:42:07 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[prc_PRM_SharePointRecoverableObject_Update]

    (

    @Caption nvarchar(40),

    @ComponentType nvarchar(16),

    @RecoverableObjectId BIGINT

    )

    AS

    DECLARE @error INT,

    @rowCount INT

    SET @error = 0

    SET NOCOUNT ON

    UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption

    UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption,

    ComponentType = @ComponentType

    WHERE RecoverableObjectId = @RecoverableObjectId

    SELECT @error = dbo.udf_DPS_CheckRowCount(1)

    SET NOCOUNT OFF

    RETURN @error

    To:

    USE [DPMDB]

    GO

    /****** Object: StoredProcedure [dbo].[prc_PRM_SharePointRecoverableObject_Update] Script Date: 11/03/2012 01:36:08 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[prc_PRM_SharePointRecoverableObject_Update]

    (

    @Caption nvarchar(40),

    @ComponentType nvarchar(16),

    @RecoverableObjectId BIGINT

    )

    AS

    DECLARE @error INT,

    @rowCount INT

    SET @error = 0

    SET NOCOUNT ON

    -- UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption

    UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption,

    ComponentType = @ComponentType

    WHERE RecoverableObjectId = @RecoverableObjectId

    SELECT @error = dbo.udf_DPS_CheckRowCount(1)

    SET NOCOUNT OFF

    RETURN @error

    After that we had a successful SharePoint catalog task that didn’t cause TempDB or DPMDB transaction log to grow.

    http://itcalls.blogspot.com/2012/11/system-center-dpm-2012-dpmdb_22.html



    Please remember to click “Mark as Answer” on the post that helps you

    My blog: http://itcalls.blogspot.com/


    Saturday, November 03, 2012 2:11 PM
  • If you are applying the script referenced in this thread there is an additional script that you need to execute after applying it.

    This script will cause a new catalog check point to be synchronized with Sharepoint the next time the datasource is synchronized.

    This is scheduled to be fixed in Update Rollup 4

    USE [DPMDB]
    GO
    UPDATE tbl_PRM_DatasourceConfigInfo
    SET ROCatalogCheckPoint = NULL
    WHERE DatasourceId IN
    (SELECT DISTINCT ReferentialDatasourceId FROM tbl_IM_ProtectedObject
    WHERE ReferentialDatasourceId <> NULL)
    GO
    

    Thanks,

    Patrick Lewis| patlewis@microsoft.com

    Senior Escalation Engineer, System Center Global Escalation Services

    Thursday, December 06, 2012 5:42 PM

All replies

  • DJITS,

    We have the exact same issue. For the past week the temp DB has consumed all the availble disk space which of course crashes the server.

    On one of the days when this didn't happen, the dpm system becomes unresponsive around 2-3am and all the jobs just pile up. By restarting the server or killing the DPM service will correct the blockage and as above the Sharepoint catalogue fails to complete. If I run the powershell to create the Catalogue nanually, the server again becomes unresponsive. This has all happen after Rollup 3 was installed. It is very clear that when DPM runs the Sharepoint Catalogue job, something starts to go wrong.

    The system will recover it's self and then work fine until the Catalogue job runs.

    Microsoft, there seems to be a problem with the update and protecting Sharepoint!

    Does anyone in the DPM team have a fix?

    Paul

    Sunday, October 28, 2012 2:05 PM
  • Hi DJITS,

    Could you verify the size of all your DPMDB's tables ?

    To do that I use this SQL Script:

    ########################

    USE DPMDB

    Go

    declare @TableSpace table (TableName sysname, RowsK varchar(32), ReservedMB varchar(32), DataMB varchar(32), IndexSizeMB varchar(32), UnusedMB varchar(32))

    insert @TableSpace

    exec sp_MSforeachtable @command1="exec sp_spaceused '?';"

    update @TableSpace set RowsK = CONVERT(varchar, 1+convert(int, RowsK)/1024)

    update @TableSpace set ReservedMB = CONVERT(varchar, 1+convert(int,LEFT(ReservedMB, charindex(' K', ReservedMB,-1)))/1024)

    update @TableSpace set DataMB = CONVERT(varchar, 1+convert(int,LEFT(DataMB, charindex(' K', DataMB,-1)))/1024)

    update @TableSpace set IndexSizeMB = CONVERT(varchar, convert(int,LEFT(IndexSizeMB, charindex(' K', IndexSizeMB,-1)))/1024)

    update @TableSpace set UnusedMB = CONVERT(varchar, convert(int,LEFT(UnusedMB, charindex(' K', UnusedMB,-1)))/1024)

    select * from @TableSpace order by convert(int,ReservedMB) desc

    go

    ########################

    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.

    My DPM blog Yet Another DPM Blog



    Sunday, October 28, 2012 2:13 PM
  • Hi Stephane,

    Here a list of the big ones. Could i not best just deinstall rollup 3 for now?

    Thanx for helping out

    Greetings,

    DJITS

    Sunday, October 28, 2012 4:00 PM
  • Hi DJITS,

    I thinks a good idea would be to use SQL Server Profiler to take a trace during Sharepoint catalog task to see what's happened...

    45GB for logs seems to be pretty high for a DPMDB when recovery model is set to "Simple" by default. It's mean that a "hudge" SQL request (Transaction) has been made, then commited and log truncated.

    Don't know if remove rollup 3 will solve this issue.

    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.

    My DPM blog Yet Another DPM Blog



    Sunday, October 28, 2012 5:36 PM
  • Hi Stephane,

    I didn't run the SharePoint Protection Group last night and the databse stayed perfectly the same so it's definitely something related to the SharePoint Protection. I'll move the DPMDB to new storage by deinstall and reinstall and then i'll run the SharePoint Protection Group with the SQL Server Provider.

    Keep you updated.

    Edit - I Reinstalled DPM 2012 Rollup 3 on new storage, restored backup, run sync and consistency check and all was well till 11:00  evening yesterday. Then the Sharepoint Catalog job started again. This morning the DPMDB was 45GB, the Log was 45GB but the temb db (still on the c drive) was 96GB. Again no more disk space.

    Here is the result of the query again (didn't shrink the db and log this time).

    Any new ideas other then removing Rollup 3 and see if this helps?

    DJITS


    • Edited by DJITS Tuesday, October 30, 2012 7:15 AM New Facts
    Monday, October 29, 2012 7:31 AM
  • Hi Djits,

    It's seems that it's not possible to roll back the Rollup 3...

    http://support.microsoft.com/kb/2756127/en-us

    Uninstall information is only for SCOM 2012.

    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.

    My DPM blog Yet Another DPM Blog

    Tuesday, October 30, 2012 10:23 PM
  • Hi Stephane,

    So what can i do?

    It happens every day now. Yesterday i ran it manually by powershell but the same happenned. I even rebuild the index for the table.

    Greets,

    DJITS

    Wednesday, October 31, 2012 8:50 AM
  • I am getting something similar for what it's worth - but it's the DPMDB log file (not TEMPDB) that grows enormously.

    My top tables are

    TableName RowsK ReservedMB DataMB IndexSizeMB UnusedMB

    tbl_TE_TaskTrail	1148	5382	4947	408	26
    tbl_ARM_DirAndFile	21604	3209	3024	132	52
    tbl_ARM_Path	1971	1644	779	717	147
    tbl_RM_RecoverySource	282	1589	1452	105	30
    tbl_JM_JobTrail	258	1120	1072	41	6
    tbl_RM_SharePointRecoverableObject	439	953	314	622	16
    tbl_JM_JobDefinition	33	601	599	0	1
    tbl_RM_ReplicaTrail	608	514	387	47	78
    tbl_RM_RecoverableObjectFileSpec	322	265	224	2	38
    tbl_PRM_DatasetVolumesMap	261	187	167	4	15

    Last night, starting at 11pm, I watched my DPMDB log file pass writes through at over 100 megabytes/second, until it consumed over 50gb, filled the disk and burst. This is on a 29gb data file and it consumed that 50gb in around 10-15 minutes.

    Was also watching another server, that also had UR3 installed do exactly the same thing, but it only had 25gb of space to write the log to so it died earlier. Tonight, we're giving up considerably more space to the DPM logs to see where it goes.

    I took a SQL Profiler trace watching the logs expand. It was horrific, adding another 500mb every few seconds. Tonight, I'll be watching it and tracing the actual SQL going on as well.

    Doesn't seem to be sharepoint related for me, so posting this just in case it's relevant to others.


    • Edited by andreww Thursday, November 01, 2012 11:03 AM table formatting
    Thursday, November 01, 2012 10:59 AM
  • Hi Djits,

    If you are member of Microsoft TAP program, you could try to install SP1 beta and see if it solves your issue.

    I do not see any other solution than open a case at Microsoft.

    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.

    My DPM blog Yet Another DPM Blog

    Friday, November 02, 2012 2:22 PM
  • Hi Stephane,

    Thanx for all the help.

    We are not a tab member but I installed SP1 Beta anyway. I'll see this weekend if it helps. If not I might open a support call with Microsoft.

    GreetZ,

    DJITS

    Friday, November 02, 2012 3:43 PM
  • I just wanted to let you all know we are experiencing the EXACT same thing in our environment, System Center DPM 2012 with RU3, it seems it happened shortly after the RU3 install. We are using our SA account to submit a case with Microsoft to reach a resolution, anything they tell us to resolve the issue I will post back.

    Thanks

    Brent

     
    Friday, November 02, 2012 4:17 PM
  • Hi Brent,

    That would be great.

    Thanx.

    DJITS.

    Friday, November 02, 2012 4:37 PM
  • Thanks guys

    We're now running a well over 70gb set of log files, and so instead last night the temp logfile expanded to 30gb and filled the disk we hadn't moved the dpmdb log files from. One step forward, one step back.

    We're trying to solve this by throwing near-unlimited disk space at the issue just now. Also, we've moved back to Simple recovery mode, as DPM backs up a simple DB via a virtual path - whereas of course Full requires a full log copy to the same disk (expensive, when the logs grow >70gb in 15 minutes). That has helped a lot. We've also moved the DPMDB MDF to another LUN to try and get round the pressure on tempDB.

    (Also, on DPM2, the Data file is 19gb.. with 15.5gb free!!!)

    Friday, November 02, 2012 5:13 PM
  • I faced the same issue after Update Rollup 3 and we raised a case with Microsoft and the Esclation team were able to fix it. I believe it will be reported as a bug and hopefully get fixed in the SP1

    For that we modified store procedure prc_PRM_SharePointRecoverableObject_Update. (You can find it under the DPMDB - Programability - Stored Procedures), Just take a DPM backup before any change for your reference.

    From:

    USE [DPMDB]

    GO

    /****** Object: StoredProcedure [dbo].[prc_PRM_SharePointRecoverableObject_Update] Script Date: 11/02/2012 17:42:07 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[prc_PRM_SharePointRecoverableObject_Update]

    (

    @Caption nvarchar(40),

    @ComponentType nvarchar(16),

    @RecoverableObjectId BIGINT

    )

    AS

    DECLARE @error INT,

    @rowCount INT

    SET @error = 0

    SET NOCOUNT ON

    UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption

    UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption,

    ComponentType = @ComponentType

    WHERE RecoverableObjectId = @RecoverableObjectId

    SELECT @error = dbo.udf_DPS_CheckRowCount(1)

    SET NOCOUNT OFF

    RETURN @error

    To:

    USE [DPMDB]

    GO

    /****** Object: StoredProcedure [dbo].[prc_PRM_SharePointRecoverableObject_Update] Script Date: 11/03/2012 01:36:08 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[prc_PRM_SharePointRecoverableObject_Update]

    (

    @Caption nvarchar(40),

    @ComponentType nvarchar(16),

    @RecoverableObjectId BIGINT

    )

    AS

    DECLARE @error INT,

    @rowCount INT

    SET @error = 0

    SET NOCOUNT ON

    -- UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption

    UPDATE tbl_RM_SharePointRecoverableObject SET Caption = @Caption,

    ComponentType = @ComponentType

    WHERE RecoverableObjectId = @RecoverableObjectId

    SELECT @error = dbo.udf_DPS_CheckRowCount(1)

    SET NOCOUNT OFF

    RETURN @error

    After that we had a successful SharePoint catalog task that didn’t cause TempDB or DPMDB transaction log to grow.

    http://itcalls.blogspot.com/2012/11/system-center-dpm-2012-dpmdb_22.html



    Please remember to click “Mark as Answer” on the post that helps you

    My blog: http://itcalls.blogspot.com/


    Saturday, November 03, 2012 2:11 PM
  • Thanks for sharing this information Ahmed.

    Edit: This spelling mistake has been introduced by RU3... DPM2012 RU2 is fine.


    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.

    My DPM blog Yet Another DPM Blog

    Sunday, November 04, 2012 8:02 AM
  • Hi,

    Seems like installing SP1 Beta also solves this problem.

    Thanx for al your help.

    DJITS

    Sunday, November 04, 2012 9:05 AM
  • Thanks Ahmed for posting this.

    Worked for us too. Bit of a silly mistake for that to slip out in testing - they should really pull UR3 or re-release it fixed.

    Monday, November 05, 2012 9:49 AM
  • Yes, I agree, I believe they might release a fix or it will get fixed automatically in SP1.

    Monday, November 05, 2012 10:33 AM
  • We had a client with the same issue. We also called Microsoft and they gave as the same solution as "Ahmed Nabil Mahmoud Consulting" above.

    Tomorrow we know if it worked.

    • Proposed as answer by PatLewis Thursday, December 06, 2012 5:38 PM
    Wednesday, November 28, 2012 1:42 PM
  • If you are applying the script referenced in this thread there is an additional script that you need to execute after applying it.

    This script will cause a new catalog check point to be synchronized with Sharepoint the next time the datasource is synchronized.

    This is scheduled to be fixed in Update Rollup 4

    USE [DPMDB]
    GO
    UPDATE tbl_PRM_DatasourceConfigInfo
    SET ROCatalogCheckPoint = NULL
    WHERE DatasourceId IN
    (SELECT DISTINCT ReferentialDatasourceId FROM tbl_IM_ProtectedObject
    WHERE ReferentialDatasourceId <> NULL)
    GO
    

    Thanks,

    Patrick Lewis| patlewis@microsoft.com

    Senior Escalation Engineer, System Center Global Escalation Services

    Thursday, December 06, 2012 5:42 PM
  • Thanks Patrick  - we applied the fix weeks ago and didn't know about your script - should we still run it now, or are we kinda past that point safely now?

    Thursday, December 06, 2012 9:10 PM
  • hi Andrew,

    You are fine to run it now. What happens with the 2nd script is that during the next sync it forces a new catalog to get generated.

    Thanks,

    Patrick Lewis| patlewis@microsoft.com

    Senior Escalation Engineer, System Center Global Escalation Services

    Thursday, January 03, 2013 2:41 PM
  • Just FYI:

    It is Fixed in DPM 2012 Sp1, however the fix for DPM 2012 RU3 customers is described in this KB article.

    2809773 The Data Protection Manager database grows and the console crashes after installing Update Rollup 3
    http://support.microsoft.com/kb/2809773/EN-US

    Blog pointing to the same KB:

    BLOG - The Data Protection Manager database grows and the console crashes after installing Update Rollup 3
    http://blogs.technet.com/b/dpm/archive/2013/01/28/kb-the-data-protection-manager-database-grows-and-the-console-crashes-after-installing-update-rollup-3.aspx


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by ShaneB. _ Wednesday, February 06, 2013 4:34 PM
    Monday, February 04, 2013 9:08 PM
    Moderator
  • Please pay attention, I think this piece of code is incorrect. "<>" cannot be used with a NULL value. This SELECT DISTINCT statement returns no values at all. "<>" needs to be changed to "IS NOT".

    USE [DPMDB]
    GO
    UPDATE tbl_PRM_DatasourceConfigInfo
    SET ROCatalogCheckPoint = NULL
    WHERE DatasourceId IN
    (SELECT DISTINCT ReferentialDatasourceId FROM tbl_IM_ProtectedObject
    WHERE ReferentialDatasourceId IS NOT NULL)
    GO

    • Proposed as answer by Edgar Escobedo Thursday, June 06, 2013 1:07 PM
    • Unproposed as answer by Edgar Escobedo Thursday, June 06, 2013 1:07 PM
    • Proposed as answer by Edgar Escobedo Thursday, June 06, 2013 1:07 PM
    Wednesday, April 24, 2013 8:21 AM
  • bien

    Thursday, June 06, 2013 1:10 PM
  • Just wanted to update that this is NOT fixed in SP1.  I'm running SP1 UR2, and still have the same issue with the tempdb growing out of control and with the console crashing constantly, especially going to management tab.  OS is 2008 R2 SP1.
    Friday, June 21, 2013 7:55 PM
  • Hi Ajrechk,

    The broken stored procedure was only in DPM 2012 UR3.  DPM 2012 Sp1 never had that same broken stored procedure.  However, if this was an upgrade from DPM 2012 UR3 to DPM 2012 SP1 we do migrate the database, so you might want to verify if that broken SP remained after the upgrade - if so, apply the fix.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, June 21, 2013 8:37 PM
    Moderator
  • I've run the scripts in KB2809773 - how/when does the database get reduced to a normal size?
    Monday, January 06, 2014 10:51 PM
  • It doesn't - go into SQL console, and right-click on DPMDB and shrink the files manually.
    Monday, January 06, 2014 11:40 PM
  • Thank you for the fast reply!

    Shrinking reduced our 182 GB database by 2-3%. What can be done to make the size more reasonable?

    Wednesday, January 08, 2014 5:21 PM
  • I use SQL script in FULL recovery model for DPMDB

    BACKUP LOG DPMDB TO DISK = N'D:\Backup\DPMDB_log.bak'
    GO
    DBCC SHRINKFILE('MSDPM2012$DPMDBLog_dat', 1)
    GO

    from http://stackoverflow.com/questions/646845/sql-server-2008-log-will-not-truncate

    It helped me!


    Have a nice day !!! DPM 2012 R2: Remove Recovery Points


    Tuesday, March 10, 2015 11:50 AM
    Moderator