Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs
Resources for IT Professionals >
> 
> 
DPMDB Database and Log File DPM 2012 get very big with lot's of Free Space in just one day

ปักหมุด DPMDB Database and Log File DPM 2012 get very big with lot's of Free Space in just one day

  • Saturday, October 27, 2012 9:43 AM
     
     
    Sign In to Vote
    0

    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
    •  
     

All Replies

  • Sunday, October 28, 2012 2:05 PM
     
     
    Sign In to Vote
    0

    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:13 PM
     
     
    Sign In to Vote
    0

    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 4:00 PM
     
     
    Sign In to Vote
    0

    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 5:36 PM
     
     
    Sign In to Vote
    0

    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



     
  • Monday, October 29, 2012 7:31 AM
     
     
    Sign In to Vote
    0

    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
    •  
     
  • Tuesday, October 30, 2012 10:23 PM
     
     
    Sign In to Vote
    0

    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

     
  • Wednesday, October 31, 2012 8:50 AM
     
     
    Sign In to Vote
    0

    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

     
  • Thursday, November 01, 2012 10:59 AM
     
      Has Code
    Sign In to Vote
    0

    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
    •  
     
  • Friday, November 02, 2012 2:22 PM
     
     
    Sign In to Vote
    0

    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 3:43 PM
     
     
    Sign In to Vote
    0

    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 4:17 PM
     
     
    Sign In to Vote
    0

    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:37 PM
     
     
    Sign In to Vote
    0

    Hi Brent,

    That would be great.

    Thanx.

    DJITS.

     
  • Friday, November 02, 2012 5:13 PM
     
     
    Sign In to Vote
    0

    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!!!)

     
  • Saturday, November 03, 2012 2:11 PM
     
     Answered
    Sign In to Vote
    15

    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/


     
  • Sunday, November 04, 2012 8:02 AM
     
     
    Sign In to Vote
    1

    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 9:05 AM
     
     
    Sign In to Vote
    0

    Hi,

    Seems like installing SP1 Beta also solves this problem.

    Thanx for al your help.

    DJITS

     
  • Monday, November 05, 2012 9:49 AM
     
     
    Sign In to Vote
    0

    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 10:33 AM
     
     
    Sign In to Vote
    1

    Yes, I agree, I believe they might release a fix or it will get fixed automatically in SP1.

     
  • Wednesday, November 28, 2012 1:42 PM
     
     Proposed
    Sign In to Vote
    0

    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
    •  
     
  • Thursday, December 06, 2012 5:42 PM
     
     Answered Has Code
    Sign In to Vote
    2

    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 9:10 PM
     
     
    Sign In to Vote
    0

    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, January 03, 2013 2:41 PM
     
     
    Sign In to Vote
    0

    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

     
  • Monday, February 04, 2013 9:08 PM
    Moderator
     
     Proposed
    Sign In to Vote
    0

    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
    •  
     
  • Wednesday, April 24, 2013 8:21 AM
     
      Has Code
    Sign In to Vote
    0

    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