Growth of QPR Data Base

4 posts 0 new
Log in or register to post comments.

Growth of QPR Data Base

The QPR database file .mdf occupy a lot of hard disk space, how I can reduce the size of this file?. There is some option in the application that can reduce the database (by deleting the old records or other way?).

Thanks in advance.

Some common reasons to big database growth is 

- embedded documents or pictures in the models or QPR Portal actions
- big changes logs in EA/PD models
- If version management is used in EA/PD models and you have the setting that new version is saved each time model is saved, then there migh be hundreds of versions of different models and that causes the big database growth.

With the below SQL query you can check how much changes logs, embedded pictures and embedded documents different QPR EA/PD models have at the moment

-----

SELECT        PG_MODEL.MOD_ID, PG_MODEL.MOD_DBNAME, pictures.[Embedded pictures (MB)], InfoItems.[Embedded InfoItems(MB)], Changeslog.[Changes log size(MB)]

FROM            PG_MODEL 
INNER JOIN 
(  SELECT SUM(DATALENGTH(EGR_DATA)) / 1048576.0 as 'Embedded pictures (MB)', EGR_MOD_ID
   FROM [dbo].[PG_ELEM_GRAPH_PROP]
   GROUP BY EGR_MOD_ID) as pictures ON PG_MODEL.MOD_ID = pictures.EGR_MOD_ID
INNER JOIN
(   SELECT SUM(DATALENGTH(AT2_VAL_BLOB)) / 1048576.0 as 'Embedded InfoItems(MB)', AT2_MOD_ID
   FROM [dbo].[PG_ATTRIBUTE_2] GROUP BY AT2_MOD_ID) as InfoItems ON PG_MODEL.MOD_ID = InfoItems.AT2_MOD_ID
INNER JOIN
(    SELECT SUM(DATALENGTH(MOD_LOG_DATA)) / 1048576.0 as 'Changes log size(MB)', MOD_ID
    FROM [dbo].[PG_MODEL] GROUP BY MOD_ID) as Changeslog ON PG_MODEL.MOD_ID = Changeslog.MOD_ID
                       
                      
GROUP BY PG_MODEL.MOD_ID, PG_MODEL.MOD_DBNAME, pictures.[Embedded pictures (MB)], InfoItems.[Embedded InfoItems(MB)],Changeslog.[Changes log size(MB)]

ORDER BY pictures.[Embedded pictures (MB)] + InfoItems.[Embedded InfoItems(MB)] + Changeslog.[Changes log size(MB)] desc

----

Hello Mika,

it is the very good QPR SQL query. Thank you very much for it.

Best regards,

Jiri SedlacekLBMS, Czech Republic

 

Thanks Mika for your response