Hi all, one of our company assigned all items to all sites & vendors and the database grew to 100GB with just master records. I deleted around 70M records from table IV10200 but the DB file size didn't change. I read somewhere that I need to use DBCC SHRINKDATABASE to decrease the actual DB file size. Feels like this forum isn't the right place to ask this question but has anyone here tried shrinking their GP database? Thanks in advance and good day everyone!
*This post is locked for comments
Hi Mello,
That is good news.. Just out of curiosity, how long did the task take to reduce from 70GB to 9GB ? Did you perform the shrink in a single step ?
The set of a pre-defined DB file might prove to be usefull in very large databases that are accessed intensively, espiecially with 'write' actions.
I've been working with GP for almost 14 years and never seen a performance issue with the DB set to 'autogrowth', but then again, it depends on your disk subsystem where your MDF & LDF files are stored.. in my case, with networked SAN or NAS HDD's, I never had an issue. I think that out-of-the-box, the default settings when the GP databases are created, is to set the autogrowth to 20% for the MDF file and 25% for the LDF file(s).
This goes without saying that a close monitoring of your disk space is more important than anything else, as you don't want your databases run out of disk space... We use a domain-wide job that reports short disk space every 4 hours by e-mail, allowing us to spot quickly unusuall situations.
Thank you all for your response. I tried the ff steps on a 70GB Test Database after deleting 70M+ records on table IV00102.
DBCC SHRINKDATABASE (<name of test DB>)
Reorganize All Index of table IV00102
Rebuild All Index of table IV00102
And the file size decreased from 70GB to 9GB! Now I am still having the end-users check data and test the performance before applying it to live DB.
Again, i read somewhere this line-
"I would also recommend that after you shrink, you re-grow the files so that you have some free space. That way, when new rows come in, they don't trigger autogrowth. Autogrowth has a performance cost and is something you would like to avoid (through proper database sizing) whenever possible."
tho I'm not sure if I really need to do that.
Hi Mello,
Restarting you SQL instance won't make a difference.. but you could effectively try to shrink your GP database by steps, not taking all the free space back in one shot, as this might leave your SQL server busy for quite some time..
Here is technical guideline on how to do it :
www.mssqltips.com/.../incrementally-shrinking-a-large-sql-server-data-file-using-powershell
As with many SQL Server admin topics, this can be a complicated subject to get into.
However to keep the reply simple, for a "one off" shrink, to recover the space from this unusual event (creation and deleting the records), you will be fine shrinking the DB and files to recover the space.
Tim.
Hi Mello ,
I'm with Richard on this one. Since you said you removed some 70M records, the backup should be fairly reasonable when compressed and you could restore it into a test company and try the DBCC SHRINK command and see how much space you recovered. I would run re-index after the shrink and you should be fine after that.
There are pros and cons to shrinking SQL databases. The good side is that you do recover disk space but the down side is that you open your database to fragmentation. I would try doing this in a text environment first and then running some SQL tools to see how fragmented it becomes. I would also check out some SQL forums and ask the same question. You would get opinions from both sides.
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...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (