SCCM 2007 SQL table has grown too big

Our SCCM database seems to have become too big over the last 6 months.
On checking (disk usage by top tables),  the table   dbo.INSTALLED_EXECUTABLE_HIST   takes up half the database size - 69Gb. I can't find any reference to what populates this database. Probably software inventory, but I'd like to know for sure if anyone can help? Then I can better tailor the frequency of the maintenance task and possibly reconfigure some of the client settings to reduce what is collected.

Thanks
AGoodwin42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

George SimosIT Pro Consultant - IT Systems AdministratorCommented:
What settings do you have now in the Maintenance Tasks of ConfigMgr?
I also hope that you are backing-up your Database regularly via the internal ConfigMgr tasks so you can roll-back when needed.
0
George SimosIT Pro Consultant - IT Systems AdministratorCommented:
Hi again,

It seems that your issue lies in the data gathered from the Asset Intelligence component and the data you are keeping for historic reasons.
I would assume that your maintenance tasks for asset intelligence have long retention periods or they are not enabled at all!
You can find easier the problem in tables by choosing to see their dependencies, after that you will also see in which views they are used, views are prefixed as: dbo.v_XX_XXXXXXXXX_XXXXX where XX denotes the type, for example dbo.v_GS is for Hardware Inventory.
Also every Hardware Invnetory view has a history view too and is prefixed as dbo.v_HS_XXXXXXXX_XXXXXXX (where the latter two parts are the name of the component).
You can find more here but remember to ommit the dbo. prefix because views are discussed as v_XX_XXXXXXXXX_XXXXXXXX in the official docs:

1.

Configuration Manager Hardware Inventory Views

2.

http://technet.microsoft.com/en-us/library/dd334669.aspx

3.

Configure Asset Intelligence maintenance tasks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AGoodwin42Author Commented:
Hi, yes it was in Asset Intelligence. We have deselected SMS_InstalledExecutable, and after a couple of days the amount of space used has decreased from 62Gb to 58Gb, I will monitor and check to see if this trend downwards continues. Thanks. Anne
0
MSSPs - Are you paying too much?

WEBINAR: Managed security service providers often deploy & manage products from a variety of solution vendors. But is this really the best approach when it comes to saving time AND money? Join us on Aug. 15th to learn how you can improve your total cost of ownership today!

George SimosIT Pro Consultant - IT Systems AdministratorCommented:
Thanks for accepting my answer Anne!
Keep in mind that you have to setup your maintenance tasks too in order to keep your DB in great shape.
I've provided some info above.
0
AGoodwin42Author Commented:
Thanks, The Maintenance tasks were already set up as per Microsoft recommendations with a max 90 days on retention of this sort of data.
0
George SimosIT Pro Consultant - IT Systems AdministratorCommented:
Glad to hear that!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.