45
My company is using Management Reporter 2012 Version 2.12.16. We notice that the database on our server is growing due to the ManagementReporter_Primary database table. I have read some threads from the forum suggesting to remove the versions from Report Viewer but I do not see any change on reducing the size of the database. May I know if there are additional steps that need to be done or is there any other suggested solutions?
Thanks
Hi Beat,
Thank you for your usual support. I have run the sql as suggested and below are the results. I'm not too sure how to read these results but the total_space_kb doesn't seem to be huge here. However, it looks like only 1 of the tables has been accessed since it was created, so I'm not sure how should I further investigate this case. Really appreciate your advise on this.
| table_name | schema_name | database_name | total_space_kb | total_space_mb | used_space_kb | used_space_kb | unused_space_kb | unused_space_mb | indexes_count | row_count | no_reads | no_writes | user_seeks | user_scans | user_lookups | user_updates | last_user_seek | last_user_scan | last_user_lookup | last_user_update | create_date | modify_date |
| AF00100 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 9:03:33 | 2018-01-23 9:07:59 |
| AF10000 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:00 | 2018-01-23 9:07:58 |
| AF40100 | dbo | INC | 224 | 0.22 | 56 | 0.05 | 168 | 0.16 | 4 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:00 | 2018-01-23 9:07:58 |
| AF40101 | dbo | INC | 80 | 0.08 | 24 | 0.02 | 56 | 0.05 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:00 | 2018-01-23 9:07:58 |
| AF40102 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:00 | 2018-01-23 9:07:58 |
| AF40103 | dbo | INC | 80 | 0.08 | 24 | 0.02 | 56 | 0.05 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 9:03:30 | 2018-01-23 9:07:59 |
| AF40104 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 9:03:30 | 2018-01-23 9:07:58 |
| AF40105 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 9:03:30 | 2018-01-23 9:07:59 |
| AF40106 | dbo | INC | 152 | 0.15 | 40 | 0.04 | 112 | 0.11 | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 9:03:32 | 2018-01-23 9:07:59 |
| AF40107 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 470 | 19 | 479 | 0 | 15:11.2 | 57:40.7 | 15:11.2 | NULL | 2018-01-23 8:58:01 | 2018-01-23 9:07:58 |
| AF40108 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:01 | 2018-01-23 9:07:58 |
| AF40109 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:01 | 2018-01-23 9:07:58 |
| AF40110 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:01 | 2018-01-23 9:07:58 |
| AF40200 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 8:58:01 | 2018-01-23 9:07:58 |
| AF40201 | dbo | INC | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | 2018-01-23 9:03:29 | 2018-01-23 9:07:58 |
Even if you schedule a maintenance task to shrink your MR database, it's not going to reduce a lot, since your Data space is still using up 95% of your reserved space (about 38GB).
To me there is another problem with your MR DB, that is likely hard to going to resolve here.
Can you find out what are the largest tables in your MR DB ? add in SSMS the columns for Rows & Size and sort them in the SQL Studio view or run this script.
-- Use the query below to know the top 15 largest tables in a Database--select top 15 tables.[name] as table_name, schemas.[name] as schema_name, isnull(db_name(), 'Unknown') as database_name, sum(allocation_units.total_pages) * 8 as total_space_kb, cast(round(((sum(allocation_units.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as total_space_mb, sum(allocation_units.used_pages) * 8 as used_space_kb, cast(round(((sum(allocation_units.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as used_space_mb, (sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8 as unused_space_kb, cast(round(((sum(allocation_units.total_pages) - sum(allocation_units.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as unused_space_mb, count(distinct indexes.index_id) as indexes_count, max(dm_db_partition_stats.row_count) as row_count, iif(max(isnull(user_seeks, 0)) = 0 and max(isnull(user_scans, 0)) = 0 and max(isnull(user_lookups, 0)) = 0, 1, 0) as no_reads, iif(max(isnull(user_updates, 0)) = 0, 1, 0) as no_writes, max(isnull(user_seeks, 0)) as user_seeks, max(isnull(user_scans, 0)) as user_scans, max(isnull(user_lookups, 0)) as user_lookups, max(isnull(user_updates, 0)) as user_updates, max(last_user_seek) as last_user_seek, max(last_user_scan) as last_user_scan, max(last_user_lookup) as last_user_lookup, max(last_user_update) as last_user_update, max(tables.create_date) as create_date, max(tables.modify_date) as modify_date
from
sys.tables
left join sys.schemas on schemas.schema_id = tables.schema_id left join sys.indexes on tables.object_id = indexes.object_id left join sys.partitions on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id left join sys.allocation_units on partitions.partition_id = allocation_units.container_id left join sys.dm_db_index_usage_stats on tables.object_id = dm_db_index_usage_stats.object_id and indexes.index_id = dm_db_index_usage_stats.index_id left join sys.dm_db_partition_stats on tables.object_id = dm_db_partition_stats.object_id and indexes.index_id = dm_db_partition_stats.index_id
group by schemas.[name], tables.[name], isnull(db_name(dm_db_index_usage_stats.database_id), 'Unknown')
order by 5 desc -- order by size--order by 11 desc -- order by row count
Hi Beat,
I have tried using the SQL to run the free space as suggested and below is my result.
Reserved Data Index_size Unused
39063976KB 38011320KB 157056KB 895600KB
It looks like my Reserved and Data took up the most space but how can I find out what are the content inside Reserved and Data?
I have already removed at least 90% of the historical versions but the size doesn't seem to shrink.
As you have mentioned to "schedule a task in Maintenance of SQL to run a DB shrink against MR database", may I know what it will do to shrink the DB?
Don't be concerned by the DataMart database, as you have no control over the content of this DB.. This DB is holding the financial data from your GP companies, and it technically doesn't grow much, only over time with the more GL data you add to GP, the more financial data ends up the DataMart.
As for the Management Reporter Database, once you have cleared out most of your historical data, you should start getting some empty space inside of it.. See my previous replies about how to get that information from your SQL server..
Make use of the sp_SpaceUsed command to find out how much free space is available in the DB.
You can then schedule a task in the Maintenance of SQL to run a DB shrink against the MR database and see if that helps. Don't run that during business hours, as it could take a significant portion of your SQL resources.
Hi Beat,
I have removed about 1000+ report versions on the Reporter Viewer and each report is approximately 30000KB so it should remove about 30GB but apparently it's not reflecting on my folder, so I wonder if there is any other indexing steps that I need to perform after removing the historical versions.
I saw some post talking about DataMart but I'm not sure if that is something that I need to run from there.
Thanks.
sorry, the previous reply didn't wanted the clipped screenshot.. so I have to paste in full text..
database_name database_size unallocated space
ManagementReporter 4943.00 MB 1.43 MB
reserved data index_size unused
5030664 KB 4824712 KB 28912 KB 177040 KB
Hi,
As Greg suggested, once you've removed enough historical versions, you can then reduce the size of the database..
That is best left to the IT staff in charge of the SQL server if you're not familiar enough with SQL Studio Management, as you could cause quite some damage otherwise..
One thing that could help to asses your current 'empty' space is to run the following query in SSMS against the MR Database:
Use ManagementReporter
exec sp_spaceused
The result returned should look like this :
Thank you for all the responses.
I have actually already removed about half of the historical report versions from the MR viewer but my ManagementReport_Primary database size does not seem to reduce. Is it have to be clean up from the back-end? As I'm not an advanced user on SQL, I would like to know whether there is a front end solution.
Dear SiuKK,
As Greg already described it, the issue with MR 2012 is that there is no automated clean-up process to get rid of old generated content.. Each single report you generate in MR under a single name can have multiple historical versions, and my experience is that MR users aren't aware about that feature. Many times, instead of going back to a previous historical version of a report for a past month or quarter, they simply re-generate the same report with the same dates/periods, and that just creates another version..
In MR viewer, you have to right-click on any report in the library and select 'Versions' to get the full list of the reports that were generated. You can then select multiple versions at a time and then Delete them from the report history.
This can be quite a lengthy process, especially if you have hundreds of different reports generated under different names.
This other MR thread might help, as it contains a SQL script to clear out old report versions from the back-end. community.dynamics.com/.../mr-clean-up-old-reports
Be careful and do not try this on your production environment before making a full backup of MR Database. The best would be if you have a dedicated TEST environment with a TEST MR setup where you can try out the effect of the scripts and then make sure your MR is still fully functional. If the scripts error out in some way and only partial data is removed, then you're up for quite some troubles as you'll end up with orphaned MR reports with no data.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,532
Super User 2024 Season 2
#2
Martin Dráb
228,501
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (