PROCEDURE pub.MSP_WEB_SP_SEC_PSMODE_PERM_SYNC_DeleteAllOldPermissionStates filled 200 GB in log database

Question

Hello I have a Project Server 2013 with SQL Server 2014 environment and lately the ProjectWebApp database grows up to 50-100 GB every night. Reviewing I found that PROCEDURE pub.MSP_WEB_SP_SEC_PSMODE_PERM_SYNC_DeleteAllOldPermissionStates is the culprit, and table MSP_WEB_SECURITY_PSMODE_PERMISSION_SYNC_STATES grows too from less 1 gb to 51 gb. We currently have more than 2000 projects and more than 1000 users (active and inactive) in Project Server but I do not find it normal for the log to grow so much. Additionally, database recovery model was changed to Simple and the growth of the log was the same. I appreciate if someone can guide me to understand why this happens.

Answer 1

Hey,

Your advice helped a little in terms of understanding the problem in a general sense.

But I found a very cardinal and dangerous solution - I made a drop and create table inside MS SQL Studio at my own risk. But I made a backup of this database beforehand. So far everything looks good. But this table stopped filling, it stopped having any data. But my site still works without errors. Stopped getting error notifications...

I don't recommend this method to anyone, but it "helped" me.
Still, it is worth discussing this method with MS Project 2013 specialists.

Answer 2

I hope that you understand that setting the recovery model to simple will void you of the possibility to a point-in-time restore in case of a failure. Have you checked whether it is compliant to your SLA to use simple recovery.

You say that the log grows 50-100 GB every night. Is that because you shrink it every day? In such case, stop shrinking it, and it will stop growing.

As for why the procedure explodes that table and the log requires knowledge of the application, so for that point you are better off asking in a forum where they know Project Server.

Answer 3

Hi,

>Reviewing I found that PROCEDURE pub.MSP_WEB_SP_SEC_PSMODE_PERM_SYNC_DeleteAllOldPermissionStates is the culprit, and table MSP_WEB_SECURITY_PSMODE_PERMISSION_SYNC_STATES grows too from less 1 gb to 51 gb

The database growth you said seems to be because the table is inserted into a large amount of data every night. So the data files and transaction logs of the database grow rapidly in a short period of time.

As for why the table accumulates a large amount of data, it is necessary to clarify whether this is a normal data record. If it is not, you may need to consult an expert in Project Server to diagnose the cause. I am not familiar with Project Server.

If the log file continues to grow even when the database is set to the simple recovery model, you need to consider whether there is a transaction that has not been committed for a long time or a large transaction is running on the database.

Please check the following update for Project Server 2013, it mentioned the table MSP_WEB_SECURITY_PSMODE_PERMISSION_SYNC_STATES. Also make sure to install the latest updates for your Project Server 2013.
https://support.microsoft.com/en-us/topic/february-21-2017-update-for-project-server-2013-kb3141525-4a5bd35c-7c45-7883-2365-fc51a0f71194