Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power BI cause temp on-premise SQL datbase growth rapidly

I have connected power bi with (query by view) sql server on-premise for weeks.

Now, I found out that my temp database log file growth rapidly approx. 30 GB in 2 weeks (system database with autogrowth by 1MB and simple recovery mode)

and this sql server have been used for 4 yrs without any growth rate. I try to shrink my almost no effect at all.

 

I also found that those query use a lot of reads and logical reads. Can I assume that whenever i execute report from power bi, there will be a cache data store in temp database.  Any solution to clear those cache?

 

Any suggestion or any advice where i can fix this or find out more information for you?

 

 

 

 

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous ,

    Yes, the database log file will record when you have query in Power BI.

    For your scenario, I'm afraid that is not related with Power BI. You should have a contact with your database admin to check if the growth is affected by powr bi.

    If it is, you could consider to disable the monitor or set the clear for those cache. 

    Best  Regards,

    Cherry

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Sorry, but i didn't sure what u actually mean.

      "Yes, the database log file will record when you have query in Power BI." -> If this mean that every time i refresh data in power bi, database log will increase due to cache data from the execution?

       

      or

       

      "For your scenario, I'm afraid that is not related with Power BI. You should have a contact with your database admin to check if the growth is affected by powr bi." -> My sceanario? refresh data from View in database will not increase database log but create query in power bi does?

       

      Btw I'm a database admin, after monitor database by following steps i found this session from power bi and system temp database log size in SQL growth about 20GB

       

      My Step for monitor growth of system temp database 

      1. shrink temp database (system database in sql) both data and log and force user to close power bi app (if user didn't close app, shrink fn will almost effect nothing)

      • Disk space available before shrink : 3 GB
      • Disk space available after shrink : 23 GB (Free up space approx. 20GB)

      2. Let user open power bi and refresh data

      •  Disk space available in temp database sizing is all gone to 0kb
      • Found process session hanging in sql activity monitor as following attached photos 

      https://1drv.ms/u/s!AgdnNyWHfFkyhXBPHktOx1mO686s

      https://1drv.ms/u/s!AgdnNyWHfFkyhXFv9UnaaqeqqYNQ

       

      Please help me solve this problem, because if not. No one in my department will use power bi for visualization or report anymore.

       

      Any additional info you need to know pls tell me.

       

      Hope to here from you soon.