topic Re: BIReportServer SQL Database purge in Report Server

BIReportServer SQL Database purge

Anonymous — Tue, 06 Jul 2021 04:55:36 GMT

How can we purge data from the PowerBI SQL database?

i would like to clear out data from our Dev server, it is currently 33GB in size

Re: BIReportServer SQL Database purge

d_gosbell — Tue, 06 Jul 2021 22:49:07 GMT

It depends on what you mean by purge.

If you don't want any of the data the fastest way to clean it all out would be to drop the databases completely and then use the Report Server Configuration Manager to generate new emtpy ones.

If you want to keep some of the data then you would have to delete any reports you don't want from the portal then you could use SQL Server Management Studio (SSMS) to do a shrink operation on the databases to reclaim space.

Re: BIReportServer SQL Database purge

Anonymous — Tue, 06 Jul 2021 22:59:02 GMT

i want to keep all the reports, but i feel like i could purge

is there any harm in this?

Re: BIReportServer SQL Database purge

d_gosbell — Tue, 06 Jul 2021 23:18:03 GMT

Based on that 1 table if you did a shrink the maxiumum space you could reclaim is the 9Mb of Unused space so it would not really be worth doing

Re: BIReportServer SQL Database purge

Anonymous — Tue, 06 Jul 2021 23:24:31 GMT

yeah, so any issues with purging the data from this table?
and then free up the space

Re: BIReportServer SQL Database purge

d_gosbell — Wed, 07 Jul 2021 00:31:00 GMT

Anonymous wrote:

yeah, so any issues with purging the data from this table?
and then free up the space

So if you are talking about truncating the table (eg deleting all the rows) yes there would be an issue. Deleting anything directly from the database is not supported and is highly likely to cause issues. Your entire instance might stop working or all your reports might thow errors. You should only ever delete things from the UI or using the support API methods.

The extended content table stores the content for your pbix based reports and the data models are loaded in and out of RAM dynamically from there. So if you delete anything from there you will almost certainly loose reports from the portal.

If you are just talking about performing a shrink operation at the database or table level there is not much danger in that although it is probably something you would want to do while the instance is not being actively used to minimize the impact. Although as I already mentioned you would only regain a maximum of 9Mb from shrinking that table.

Re: BIReportServer SQL Database purge

Anonymous — Wed, 07 Jul 2021 00:56:09 GMT

ok thanks

so what in your opinion is the best method to reduce the size of the database moving forward?