DPMDB Maintenance Part 3: Dealing with a large DPMDB

First published on TECHNET on Apr 01, 2015

~ Chris Butcher | Senior Support Escalation Engineer

Hi everyone, Chris Butcher here again with part 3 in my series of posts on DPMDB maintenance. This article addresses DPMDB size and the possibility to shrink. I heavily leveraged some information already put together by one of my colleagues in the UK, Emily McLaren, so she deserves credit for much of this information. And “heavily leveraged” in many parts means I have used her exact information including screen shots, so thanks Emily!

In case you missed parts 1 and 2 you can find those here:

DPMDB Maintenance: Database consistency check and your DPMDB

DPMDB Maintenance: Identifying and dealing with fragmentation in your DPMDB

You can also consider the blog post by Mike Jacquet below to be part 4 which completes this series:

How to protect your Data Protection Manager SQL database

DPMDB Size

While most people may not realize that there is database corruption (out first post), or that their performance has slowly been degrading (our second post), we do get quite a few questions about the size of the DPMDB. Is it normal that it is so large? Why does it get so large? Is there anything we can do to shrink it?

These are all valid questions so let’s first start with the actual size. There is no guideline that will tell you how large you should expect yours to be. This is driven by a couple of major factors like retention range and what type of data you are keeping. It is normal for sure that these can grow to be quite large, and I regularly see them over 100GB, so a large DPMDB is not something to necessarily worry about.

What we can do, however, is look at what may be causing the large size and address that. There are some issues where you can decide how much data DPM should actually retain which can help with keeping the size as small as possible.

To get started, we need to open SQL Management Studio from the SQL server that houses the DPMDB. Right click on the DB in question and select properties.

If the DB is large you will then want to identify which table(s) in the DB are responsible. Large can be fairly subjective, as certain configurations (e.g. protection of large SharePoint Farms) will naturally lead to DB growth. If it is causing issues with disk space or there are performance issues, then the following steps should be followed regardless.

To find out which tables are large, run the Disk Usage by Top Table report against the DPMDB:

Based on the output of this report, which should list the tables in order of size (descending), take a look at the top tables. Below I listed some of the tables that are most frequently large in size.

Tbl_RM_SharePointRecoverableObject

This table can become large when large SharePoint farms are being protected (e.g. farms with millions of items), or multiple farms are protected.

The following formula will approximately tell you how big the DPMDB can get when protecting a large SharePoint farm:

((Number of items in the Farm in millions) x  3) + ((number of content DBs x Number of SQL servers in farm x 30) / 1024) = size of DPMDB (GB)

This only takes into account the SharePoint related DB growth though, so it may be larger than this value overall, meaning you may need to work out the size per farm and then total them. Unfortunately there is not much we can do to reduce the disk space used by the DB in this scenario.

Tbl_ARM_DirAndFile and/or Tbl_ARM_Path

The tape catalog pruning settings can cause these tables to be large. To reduce the size of these tables, modify the tape catalog retention values to reduce the amount of data we are storing in the DPMDB:

The default is to allow it to remove the entries as the tapes expire. If tapes are kept for multiple years, this can lead to a large amount of data retained in the DB. Change the settings to “Prune catalog for tapes older than” and set it to a sensible value (e.g. one month).

Updating this will not delete data on tape, but will mean that tapes older than this value will need to be re-cataloged in order to restore data from them.

Tbl_TE_TaskTrail

If this table is growing large then it is generally a sign that the overnight jobs to clean up the DB are failing, as garbage collection should clean up any data older than 33 days in the task trail table. To check if garbage collection is doing its job, we need to run a query on the SQL server where DPMDB resides. Open SQL Management Studio and run the following query:

use DPMDB --you will need to put in your DPMDB name here

This will tell you how many entries there are that are greater than 33 days old. If 0 then GC is clearly working. However, if you see this return some entries, you will likely want to open a case with our support teams to help determine what is going on there.

TempDB Bloating

This is not as common as bloating of the DPM DB, but it can be a cause/symptom of some performance issues. Equally, if you are trying to understand where the disk space on the DPM server is going it could be being used up the TempDB.

What is the TempDB?

Briefly, TempDB is used for temporary objects (as one would expect) that are being used whilst queries are running. These variables or tables may be explicitly created by a query (e.g. a stored procedure generating output) or implicitly created (e.g. temp tables for sorting objects). This is useful to know in order to understand why it may be getting large.

If you want to know more about the TempDB there is further detail on MSDN here:

SQL Server tempdb Database : http://msdn.microsoft.com/en-us/library/ms190768.aspx

Why might it grow?

It could be simple in that we have a query working with a large amount of data, however this is not likely to be the case if it is growing by several GB. More commonly though, with DPM it is caused by a long running transaction. This can prevent cleanup of the transaction log which causes it to continue growing until the transaction completes, or indefinitely if it never completes.

Also, if other databases are collocated on the same instance, it may not be DPM at fault, as the TempDB is available to all DBs on an instance. For example, if another application is causing a problem with the TempDB it could therefore impact on DPMs console performance. By this same rationale, if you are sharing an instance with other DPM servers they all can be using it at the same time and thus growth should not be a surprise.

How do we tell if there is a problem?

First, in SQL management Studio open the TempDB properties:

If it is GB in size then there is likely a problem. Note that with TempDB it is not possible to enable AutoShrink, and if AutoGrow is enabled, once the DB grows large it will not drop back in size.

Troubleshooting

In the properties you can see if there is any currently free space in the TempDB. You can see this just below the size in the Properties window. If so, it is unlikely that whatever caused the growth is currently happening. If there is available free space in the TempDB then from the SQL management console you can try to shrink the TempDB size:

If there is available free space in the DB you should be able to shrink it. If you find that the size does not decrease as much as expected, check what the initial size of the DB is set to:

Sometimes this value can be set quite large. If so, drop it back to 8Mb and try the shrink again (this will be the lower limit of the shrink).

Restarting the SQL instance will also reset the TempDB size back to its initial size.

Once you shrink the DB, monitor it to see if the growth reoccurs. If so, try to identify when the growth occurs. You can look for AutoGrow events on the TempDB in the default trace files that are created by SQL in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log (or a similar install location for the SQL instance). Identifying a particular time (or times) each day when the growth occurs is useful if you need to further engage support.

Chris Butcher | Senior Support Escalation Engineer | Microsoft GBS Management and Security Division

Get the latest System Center news on Facebook and Twitter :

System Center All Up: http://blogs.technet.com/b/systemcenter/

Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/
Data Protection Manager Team blog: http://blogs.technet.com/dpm/
Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
Operations Manager Team blog: http://blogs.technet.com/momteam/
Service Manager Team blog: http://blogs.technet.com/b/servicemanager
Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm

Microsoft Intune: http://blogs.technet.com/b/microsoftintune/
WSUS Support Team blog: http://blogs.technet.com/sus/
The RMS blog: http://blogs.technet.com/b/rms/
App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv
The Surface Team blog: http://blogs.technet.com/b/surface/
The Application Proxy blog: http://blogs.technet.com/b/applicationproxyblog/

The Forefront Endpoint Protection blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/