Skip to main content

Notifications

Microsoft Dynamics RMS forum

100GB Database size to reduce

Posted on by Microsoft Employee

Mabuhay!

Hi! We have this database size 100GB and we plans to reduce it to at least 60GB so it will runs more smoother. 

I recently run

CREATE TABLE #tmpTableSizes
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert #tmpTableSizes
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"


select  * from #tmpTableSizes
order by dataSize desc


to know how much row size does our database consist of. I deleted some transactions less than and equal to Year 2014. As of today whan I look at the database size it remains 100GB still. Or do I need to back up and restore it again so it will update the database size?

HQ2014.JPG

Am I missing something here so I can meet much lesser database size soon.

Thanks,

Chris

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: 100GB Database size to reduce

    RMS database backup scheme is by default set to "simple".  Which means the database will shrink when you run the backup if rows were deleted.  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: 100GB Database size to reduce

    i would suggest using a archiving and truncation tool to achieve this. I use a great tool from retail realm for this process which will allow you to pick how much years of information you want to keep in the live database and then junk everything else into the archive database.  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: 100GB Database size to reduce

    Mabuhay Sir Hotrod :)

    Thanks!

    What we've noticed was after deleting some journals and transactions from 2014 and older it remains 100GB and after three days it became 77GB. Does it means updating the size of the database remain the same until it completely change on the following days?

    From backup after truncating it became 55GB (compress during backup) but still 100GB on the actual DATA size.

    And now it became 77GB. Does this scenario do most of the times?

    Chris

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: 100GB Database size to reduce

    Hi Chris,

    Try to shrink your database.

    Best regards,

    Hotrod

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Give Back to the Community this Month

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,133 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans