Clarity

Hi all.

   Our full load of our data warehouse randomly fails with a

Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

error.

My hunch is my DBAs are running database maintenance at the time of the full load - Microsoft Knowledge Article KB2979636.

Question: If one runs a 'Full Load' on the data warehouse each night, is there a need for database backups or other maintenance activities such as shrinking files and indexing?

What are the best practices for maintenance of the Data Warehouse, and more specifically, what, if any, standard activities that are performed on the CA PPM OLTP database can be dropped?

Hi Robert,

The SQL server maintenance should still be run on both PPM and DWH databases, they're needed so any defragmentation is avoided. Here are our official guidelines on the topic:
http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec488108.aspx
This is applicable for both PPM and DWH.

Data Warehouse automatic backup may not be required : the Full Load of Load Data Warehouse job is recreating the data from scratch from source PPM. However some customers prefer to have a backup as well (usually when the data is large and the Load Data Warehouse job takes a while), so in case something happens, they can quickly recover and still have data to access in their reports. If your Full Load takes less time than a backup restore, that may not be needed.
Backup is also needed when you refresh another environment with PPM and DWH, as we recommend refreshing both, however for this purpose you may schedule a separate backup and that's not something that is done on a frequent basis.

Concerning your error message : I agree with you : it looks like something else is being run on the database when the job is running. Check with your DBA to find out what exactly, and try rescheduling it in a time in which the job is not running.
I think it might help to explain to DBA the concept of the DWH database : a read-only database that gets fully reloaded from PPM by the Load Data Warehouse job. The only time the data is getting updated is when the job is running. So they should just take this into consideration when configuring the database as some of the features may not be applicable.

Hope this helps -Nika

NIKA HADZHIKIDI wrote:

 

Data Warehouse automatic backup may not be required : the Full Load of Load Data Warehouse job is recreating the data from scratch from source PPM. However some customers prefer to have a backup as well (usually when the data is large and the Load Data Warehouse job takes a while), so in case something happens, they can quickly recover and still have data to access in their reports. If your Full Load takes less time than a backup restore, that may not be needed.

Thanks Nika. We're small - our Full Load takes around 10 minutes. Since I can control kicking off a new full load if needed this would surely be faster than opening a ticket to have our DBA team restore from backup - I'm going to advise them to drop the Data Warehouse from their backup routine and let's see if this clears up my errors. I'll report results back to this thread to help the next forum traveler who sees this error.

Thanks!

To close the loop - we have removed the Data Warehouse from our backup scheduled and this has resolved the issue.

Thanks!