DataBase - GL Purge - Where is our free space?

Asked By Tracy

29-Jan-10 01:21 PM

We did a Purge of our GL in Great Plains 10 (got rid of 1992 - 2006). Our
database was 40gb to start.  The issue is that the purge did not free up any
space.  So I ran a SHRINKDATABASE.

DBCC SHRINKDATABASE (N'xxxx', 0,TRUNCATEONLY)
This made our log file go from 4gb to 118mb, did not change the size of the db

DBCC SHRINKDATABASE (xxxx, 10)
This made our log file go stay @ 118mb and size of the db went down by 3.5gb

DBCC SHRINKDATABASE (xxxx, 20)
It would not run this as it reported there was not enough free

So I ran
DBCC SHRINKDATABASE (xxxx, 10) again
and it freed up another 70mb.

We expected a huge difference.  Can you tell me where we went wrong?


Thank you in advance,
Tracy

29-Jan-10 08:35 PM

Data will not be removed from transaction logs until the database is backed
up.  This is a safety precaution.  Then your tasks should work.
--
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at
http://www.AccoladePublications.com

30-Jan-10 09:52 AM

Tracy,

You mentioned the log file (.ldf) size reduction, is the 70mb reduction the
amount of the database (.mdf) size reduction? Are you dissatisfied with the
reduction in the database size or the log file size?

I think you also might try backing up you .ldf file. If you are using the
FULL recovery method, the log is not truncated at checkpoint, so you need to
back it up to clear the transactions already committed to the database.

If you have backed up the database as Richard suggested, you should not need
the transaction logs prior to the backup. So, I would backup the transaction
logs and backup the database.

Kind regards,

Leslie

02-Feb-10 07:09 AM

My procedure was to backup (via SQL tasks) the databases before and after
the purge I have not backed up since I ran the shrink.

the .ldf is 200k, my .mdf is 37.5 gb.  I am not concerned about the .ldf.
Why has my .mdf only gotten 7.5 gb smaller after the shrink?

Do I need to backup after SHRINKDATABASE?


Tracy

02-Feb-10 07:19 AM

Sorry, it had only shrank 3.5 gb in size not 7.5

03-Feb-10 10:44 AM

Hi Leslie, I am unsatisfied with the reduction of size in the database.  I
have been in SQL Managemnet Studio and shrinking both the database and File
and have got a grand total of 7gb freed up og a database that was 40 to
start.  Once the Purge ran and completed for all the years (1992 - 2006) I
did perform a backup (right click database/backup).  Since I have been
running the shrinks I have not done any more backups.


Thank you Leslie,
Tracy

03-Feb-10 11:00 AM

Tracy,

I think the question that everyone is neglecting to ask is: What portion
of your overall data is represented by the G/L history that you removed?
Think about what you removed in relation to all of the data that you
hold in the database.

If you have thousands upon thousand of customers, a huge number of
inventory items that move quite quickly (with FIFO recording), and a
multitude of vendors all transactions for which are posted into the G/L
in summary, then the G/L portion of your data would be a small part of
the overall database.

Lyle

05-Feb-10 10:31 AM

Lyle, you hit the nail on the head!  We "assumed" we'd have lots of space
freed up because our GL history went down by 1/2.  But the table (GL30000)
was only 3.5 gb to start, so it did indeed shrink by about half but it really
did not free up much space as it was not taking up much space to begin with.

We are investigating other avenues.  I have found a query online that allows
me to show the size of each of the tables on the database and that has
enabled me to better see where the space is going to.

Thank you all for your replies.