Art Of Creation – Dynamics AX Blog
The everyday life of a Dynamics AX developer
February 3, 2010 at 21:03
filed under Dynamics AX
Tagged Dynamics AX, SQL
This week, we were shrinking a database of a development environment by deleting some companies.
Here a nice little SQL statement that uses the sp_MSforeachtable stored procedure to delete all records of a specific company (CEU in this case) from all tables.
Certainly fast(er than AX) and gets the job done.
Use at your own risk ;-)
Update:
You’ll want do do some cleaning up to:
delete the company id from the DataArea table and from the CompanyDomainList table
Update 2:
When you want to delete all companies except a few (like DAT), just use this:
This will delete all companies except DAT en DEMO.
Alex
on November 30, 2010 at 18:21
There are many other tables that also have a company identifier in a field other than “DataAreaId”. You have already seen from your 2nd update the table DataArea has one called “Id” and no DataAreaId.
I’d be worried this misses some data.
Klaas Deforche
on December 2, 2010 at 11:04
Hi Alex,
The company for a record is always stored in the DataAreaId field. Other field that contain a dataAreaId are always references to a DataAreaId field.
When you delete an AX company, you can always end op with data that references a company that doesn’t exist anymore, but that will be data stored in other companies than you are deleting.
I’m fairly sure all records will be deleted for that company, but again, this is just a ‘quick and dirty’ way to do this. In a production environment, I would certainly use the standard AX way of deleting companies.
Tommy Skaue
on December 28, 2010 at 15:25
You may also have to clean up the VirtualDataAreaList table (http://msdn.microsoft.com/en-us/library/aa849574.aspx)
Henry
on July 30, 2011 at 07:36
Hi,
I have the admin privileges on my stand alone computer where i installed the Dynamics AX 2009, i created the three companies with their demo data imported, but the data didn’t imported successfully, so i deleted the three companies , after deleting the CEU company, my hard drive space of 4 GB was consumed ……. can anyone tell me what might be the problem, if there is any way to free that occupied space, Kindly resolve this problem ASAP.
Thanks in advance,
Henry
Henry
on July 30, 2011 at 07:38
this is how i deleted,
In AX, i selected the company from the company account list which is at the right bottom and right-click –> go to main table and i deleted it from there.
Klaas Deforche
on August 1, 2011 at 14:23
Hi Henry,
I agree, deleting the companies in AX is better (“cleaner”),
on sql, it’s quick an dirty.
Your disk space loss is probably due to the fact that your database size has grown, even though you deleted the data.
There is a way to shrink your database (eg: http://technet.microsoft.com/en-us/library/ms189035.aspx).
Eugene
on August 3, 2011 at 00:13
“I agree, deleting the companies in AX is better (“cleaner”),
on sql, it’s quick an dirty.”
Come on, open your database in SSMS and see if you can find any records referencing company account you deleted in AX.
I currently run upgrade validation checklist for AX 2012, and there are heaps of tables containing reference to company accounts that are loooooong gone.
So if you like it nice and clean, do it in AX, but then run a clean up job like in this post.
Thanks a lot!
Jose Luis Olabarri
on November 28, 2012 at 12:53
Please, don´t do this to delete organizations. You will have problems with the recid of the new rows inserted in the database.