1. Home
  2. Point-Of-Sale Software
  3. Database size, cutting it down shrink reindex

Database size, cutting it down shrink reindex

Our database seems to be getting to big too fast. Since January our database has grown to almost 600 mb, and it grows about

10-20 mb per day. I have decided to delete some old journal entries , old Quicksell journals . I have done this multiple times on test machine and was getting different database /backup sizes depending on order of the steps.

This seems to be the best way but makes me concern about the database. I started with a backup file with size of 569,747 I restored it to ne database on test machine

i ran SQL query

DELETE Journal WHERE time < getdate()-15 this deletes all register journals older than

15 days than i ran

run delete oldjournal delete old qs2000 journals i backed up the database and the backup file was cut down to 346,963 than a run dbcc shrinkdatabase and reindex the backup size went down to 286, 420 ran dbcc shrinkdatabase and reindex again backup went 252, 307 ran it two more times and it went to 216, 083

This makes me wonder if any other data get lost while running shring and reindex.

As far as i know shink and reindex do not remove any data so why does the database file keeps getting smaller each time i run those commands?

Has anyone ran into this problem ? Should i be concerned about running shrink and reindex ?

Is there a better way to manage database size ? Thanks

Reply to
Robert
Loading thread data ...

Hello thanks for your reply I'm running RMS 2.0.115

I understand how the database grows and that not all of the space is used by data.

What i do not understand is why when i reindex and shrink the database multiple times it keeps getting smaller.

"Jeff @ Check Po> Robert,

in SO Admin, you were asked how to increase in size when needed, by a percentage (10%) or by a fixed size (in MB). If you went by defaults, it increases by the percentage.

defaults). This does not mean that data now exists in the entire database it just means that the database added extra space to be filled with data, kind of a buffer.

empty space, so the database shrinks back to its previous level. Remember that

10% rule

Express (1.3R and above). MSDE has a size limit of 2 GB whereas SQL2k5 has a 4 GB limit. If you need more, the are 4 additional versions of SQL without size limits, none are free though.

Reply to
Robert

Hi Robert,

You may want to know which file is growing ? It may be the transaction log file. If so - You may want to check ths database recover model and set the database recovery mode to Simple. Do a backup before anything.

Best regards,

Mihir Shah Diviasoft, Inc.

formatting link

Reply to
Mihir Shah

How can i tell what is gowning ? What files should i look for

Thanks Robert

"Mihir Shah" wrote:

Reply to
Robert

dbcc shrinkdatabase picks a target size and then works to get that ending size In your case the first dbcc shrinkfile was this formula: 286 = (data + (10% of 600)) * 600 So your data size was roughly about 226 (actually it was about 200 with

10% space added to that which is final dbcc shrinkfile size after all logfiles adjusted). The next dbcc shrinkfile resulted in about 254 = (226 + (10% of 286)) * 286 Note: the percentage is not applied on the whole database, but on each individual database and logfile so it does not compute out exactly. We are now getting down close to actual file size with the next running of dbcc shrinkfile.

- Dave

Reply to
dc

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.