100GB Database size to reduce

(0) Share

Report

Posted on by Community Member 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?

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

Thanks,

Chris

*This post is locked for comments

All responses (4)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

RE: 100GB Database size to reduce

Hi Chris,

Try to shrink your database.

Best regards,

Hotrod