Cleaning Up Your SMS Database with DelGrp

By ServerWatch Staff (Send Email)
Sep 7, 2000

- Digg
- DZone
- Reddit
- Slashdot
- StumbleUpon
- del.icio.us
- Facebook
- FriendFeed
- Furl

Got a lot of unnecessary tables in your database? Don't make the mistake of deleting them with SQL Enterprise Manager. This article provides practical uses of DelGrp from the SMS 2.0 Resource Kit. Included is the batch code for automating the removal of all Dell and Altiris database objects.

Got a lot of unnecessary tables in your database? Don't make the mistake of deleting them with SQL Enterprise Manager. Dana Daugherty's latest article provides practical uses of the Delete Group Class Tool (DelGrp.exe) from the SMS 2.0 Resource Kit for deleting group classes and their tables.

Deleting tables without deleting all the pieces of the group can cause problems with dependencies, not to mention lots of errors.

The Delete Group Class Tool (DelGrp.exe) is used to delete group classes and their tables.

"Real World" Application

I can think of a few examples when this may become necessary. First, let's say you decide to evaluate certain Altiris products. Or, maybe Inventory solution or Software Delivery Solution are configured to insert data into the SMS database. Dell OpenManage Client can also cause new database objects to appear if you configure it to send a NOIDMIF.

If you have multiple versions you will have many tables, some with numbers appended to the end, like 001, or 003. If you have used both of these products there could possibly be over 200 tables and stored procedures. Now you've got a real mess that you need to clean up.

Before attempting to remove these tables be sure you have a recent backup and try it in a test lab. Losing your SMS database can really ruin your day.

Using DelGrp

Before you can use the Delete Group Class tool, you must first obtain the proper name of the group that you want to delete. You can obtain a list of group classes by running the following query in SQL:

Select GroupClass From GroupMap

After you have determined the group you want to delete, run this command:

delgrp.exe "MICROSOFT|WORKSTATION_STATUS|1.0"

*Note - delgrp.exe must be copied to the Primary Site Server where you want to modify the database and the quotations must be used.

If you have 100's of groups to delete this could get quite monotonous. I exported the list of groups from the SQL Query Analyzer and placed them in a batch file. It worked much better. I copied delgrp.exe into the same directory as these batch files (on the a site sever).

Here is a portion of the batch code that I used to remove the Dell Groups:

Here is a portion of the batch code that I used to remove the Altiris Groups:

Additional Tips:

After you run DelGrp to delete the desired groups, using SQL Enterprise Manager delete any related stored procedures using this Q article. You will find 2 stored procedures for each table that you deleted.

Q256073

The more preferred method of extending hardware inventory is to edit the SMS_def.mof file. Allowing 3rd party vendors to create a NOIDMIF file can cause unexpected results. It can be difficult and time consuming to clean up. The sms_def.mof is a database that the SMS client works through to access WMI. WMI is an industry standard. It is a more stable path to follow.

To learn more about WMI and extending your hardware inventory, take a look at the WMI SDK at http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/msdn-files/027/001/566/msdncompositedoc.xml

Also, pick up Marcin Policht's book "WMI Essentials for Windows Management" for more information on WMI.

Page 1 of 1

0 Comments (click to add your comment)