Frequently Asked Question

Best practices to clean up the P6 calendars

Last Updated 4 years ago

Primavera P6, like any other application, needs maintenance to keep its usage efficient. The P6 calendars are one of many P6 business objects, which need cleanup over time. This KB article describes how to perform a thorough cleanup.

Introduction

To have a better understanding of the P6 calendars, an introduction of the calendars is provided. P6 contains the following calendars.

Global
Resource (shared or private)
Project

The global calendar is unlimited in its scope and can be applied to several P6 objects. The resource calendar only applies to a resource, for example a person, a role, a piece of material, or a tool (ie. crane, fork lift). The project calendar can only be applied to one single project.

To safely delete calendars you have to know if the calendar is in use.

Find unused global calendars

There is no easy safe functional way to retrieve a listing of all unused global calendars. There is an unsafe way by opening all projects in the same time with sorting and grouping. However, this might crash P6 Professional and/or create database problems preventing other users to work. Therefore this will not be described any further.

There is a 'per calendar' way to determine if a calendar is in use, however this might be slow in case of a high number of calendars.

Open the [Calendars] overview:

Click on [Used By].

Alternatively, there is a technical way available to find all the unused global calendars by database query. Login with the privuser by a SQL client and execute the following query.

# Show unused global calendars
SELECT CLNDR_ID, CLNDR_NAME, CLNDR_TYPE FROM CALENDAR WHERE CLNDR_TYPE='CA_Base' AND CLNDR_ID NOT IN (SELECT BASE_CLNDR_ID FROM CALENDAR WHERE BASE_CLNDR_ID IS NOT NULL) AND CLNDR_ID NOT IN (SELECT CLNDR_ID FROM PROJECT) AND CLNDR_ID NOT IN (SELECT CLNDR_ID FROM RSRC) AND CLNDR_ID NOT IN (SELECT CLNDR_ID FROM TASK) ORDER BY CLNDR_NAME;

Find unused resource and project calendars (dangling)

Although P6 Professional and P6 Web should take care of unused resource and project calendars, sometimes (due to bugs) the calendar remains in the database. The calendar is not visible in P6 Professional and P6 Web anymore; it is being hidden. These calendars are called dangling calendars.

This can cause problems for P6 integrations, which might be unaware of dangling calendars. The P6 API and the P6 Web Services will still provide these calendars, while there is no associated resource or project anymore.

To find the possible dangling calendars, the following database queries can be used.

# Show dangling resource calendars
SELECT CLNDR_ID, CLNDR_NAME, CLNDR_TYPE FROM CALENDAR WHERE CLNDR_TYPE='CA_Rsrc' AND RSRC_PRIVATE='Y' AND CLNDR_ID NOT IN (SELECT CLNDR_ID FROM RSRC);

# Show dangling project calendars
SELECT CLNDR_ID, CLNDR_NAME, CLNDR_TYPE FROM CALENDAR WHERE CLNDR_TYPE='CA_Project' AND PROJ_ID IS NULL;

Delete calendars

To delete calendars, as any P6 object, the safest way is to delete them by the P6 application. This way the P6 business logic is honored. Deleting by the P6 application can be either by P6 Professional, P6 Web, P6 API or the P6 Web Services. This KB article demonstrates deleting by P6 Professional and in case of a dangling calendar by SQL query.

Delete global calendar

A global calendar cannot be deleted when its in use.

A global calendar can be deleted when it is unused.

Delete resource calendar

A resource calendar can be removed without issue when it is unused. When the resource calendar is in use a replacement calendar can be assigned to take its place.

Delete project calendar

When a project is deleted all related project calendars should be deleted automatically.

Alternatively it is possible to delete a project calendar in the calendar list and assign a replacement if the calendar is still assigned to any activities or resources.

Delete dangling calendar

To delete a dangling calendar, write down the unique ID of the calendar (CLNDR_ID) and replace the 457 in the following SQL query.

Warning: performing database modifications can cause problems; make sure a good database backup has been made.

# Delete a calendar
DELETE FROM CALENDAR WHERE CLNDR_ID=457;
COMMIT; # (Oracle only)