Announcements
Like many others I'm trying to reduce the size of my HQ database. I'm using SQL 2008 Express so there is a 10gb database size limit. I'm currently at about 8.5gb. I have 16 stores reporting to this HQ server and about 17000 different SKUs. About a month ago I deleted completed worksheets older than 90 days through the headquarters administrator app. I also ran a dbcheck, dbshrink, and a dbcheck again. In the end the database was around 7gb.
I started looking around today using SSMS to see which table was taking up so much space. I have never had journals upload to the server which is usually a cause for this database to grow so quickly. I have come across the HQEventLog and it is presently at a data space of 3700.664mb. I've opened the table and its first entry goes back to May 2011. Does anyone know if it would be safe to delete entries in this table say >90 days old. I see it appears to record every connection from the stores to hq and all worksheet processing between the stores and hq as well. I usually use the Event Viewer in HQ Manager to determine errors on worksheets at store level and this is where the information from this table is stored.
My theory is it shouldn't have any adverse effects by me doing this but wanted to see if anyone else had done so already. I figured on running a db backup, then running a sql statement to delete entries in the table >90 days, run a db shrink, and then a db check hoping that this all goes smoothly and that this would help gain a bit of control over the size of this database.
Let me know what you all think.
Thanks,
Chris
*This post is locked for comments
Hey Chris - This may be a bit late, but I've had similar problems and I'll share with you what I use to 'avoid' having to upgrade to full SQL. I use Retail Realms Archiving tool. It works like a champ. I am running the same DB as you and whenever I get w/in 1GB or so of the 10GB limit I run the Archiving utility and knock off 2 or 3GB. The info is just archived in another HQ accessible database that I can access for reporting purposes etc...so it is not like the info is gone forever. I had been using the same tool at the store level before the increase from 4gb to 10gb w/ the express version of SQL and it works just the same on the HQ database. BTW, I started w/ full SQL, had a 25GB+ HQ database and decided to simplify my life and ditch our Windows SBS. I downgraded to SQL Express and in order to do so, I used the archive utility to take out the extra 15GB+. The small headache w/ having some of our older data in another database is a lot less painful then footing the bill for full SQL and the required hardware the goes along w/ it. Just my 2 cents.
Not a problem, those are just for your information. To be honest 4mb isn't very much in the scheme of things. Your main culprit is the Journaled receipts both in SO and HQ. That's the biggest size wise.
Also, don't bother shrinking the database as it will take longer to re-create the space than you will to remove it. With 16 stores, you may just have to bite the bullet and buy a full copy of SQL.
I dont think deleting the HQ Event log will be a problem.
You would want to keep the last months event logs just in case you need to refer to them,
Also make sure you test it first and have a good backup.
Best regards,
Mihir Shah
Diviasoft, Inc.
That is the plan eventually...the reason I posted this was really to see if deleting older entries in this table would cause any issues as it appears to be the largest table in the HQ database and purging these older records would allow more time before having the expense of moving to the full version of SQL server.
Hi Chris,
You may consider moving to SQL server edition; eventually you will exceed 10GB with time.
Regards
No takers on this one?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,782 Super User 2024 Season 2
Martin Dráb 229,067 Most Valuable Professional
nmaenpaa 101,150