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

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

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

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

St

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

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.

Ste

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

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

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

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

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

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

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

 

Hi Brent,

That would be great.

Thanx.

DJITS.

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

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 didnt cause TempDB or DPMDB transaction log to grow.

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

Thanks for sharing this information Ahmed.

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

Hi,

Seems like installing SP1 Beta also solves this problem.

Thanx for al your help.

DJITS

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.

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

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

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

• Proposed as answer by PatLewis Thursday, December 06, 2012 5:44 PM
• Marked as answer by Mike JacquetMicrosoft employee, Moderator Thursday, December 06, 2012 6:54 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?

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

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

bien

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.

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.

I've run the scripts in KB2809773 - how/when does the database get reduced to a normal size?

It doesn't - go into SQL console, and right-click on DPMDB and shrink the files manually.