Video Screencast Help

SQL database size 56gb 230 clients.

Created: 29 Oct 2012 • Updated: 06 Nov 2012 | 25 comments
This issue has been solved. See solution.

Hello.

My Symantec_CMDB.mdf is 56GB, this seems very excessive for the amount of managed clients we have.

Is there any sql maintenance job that I can impliment to keep this size at a minimum.

Also is this sql task enough?

is there any more that I can add?

I have included a series of screenshots that will hopefully show all the details related to my CMDB configuration. Please let me know if you require any further info.

Thanks in advance.

Comments 25 CommentsJump to latest comment

mortenleth's picture

Hi Network101

I would probably start off by finding out which tables it is which are large.
You can do that by right clicking your DB and click "Reports" -> Standard reports and disk usage by top tables.

Then it's possible to truncate your tables, depending upon if it's something you want to remove, to generate space.

Evt_NS_Event_History
Evt_AeX_SWD_Status

as far as i remember these tables, can contain very much information.

Kind Regards
Morten leth

network101's picture

Hello. Thanks for the reply. I have attached the requested screenshot.

7.jpg
andykn101's picture

What sort of data is in the ec_alert_variable table, it's not one I'm familiar with. That's the one you need to turn purging on.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

network101's picture

I don't know what it is or does either. Any ideas how I would turn purging on for what ever that is?

And how come it has become so big, is it a misconfiguration?

andykn101's picture

Look at the data in it.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

andykn101's picture

 

select * from ec_alert_variable table

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

network101's picture

Hi, thanks for that. I have returned the top 1000 rows and it's all repeated from the same host SNMP data.

must have been repeatedly collecting the same snmp data from the same printer for a while.

is this safe to purge and is there a setting i can choose to prevent this filling up the database?

8.jpg
mortenleth's picture

Hey network101

Hmm, i am not sure about this, but do you have SNMP discovery enabled? - this is by the top of my head the only one i feature which uses this.

/Morten

md investigate's picture

Hi,

would have a look for alert management settings in All Settings --> Monitoring and Alerting --> protocol management --> alert management settings. Do you enabled anything? Maybe you can give us some more information about your env? version? etc.

Per default when alerting is not used the tables are empty, but something is enabled in your side. Question for me: is this wanted?

Regards

SOLUTION
network101's picture

Thanks for the details responses. My version is CMS 7.1 SP1

Following your guidance i have navigated to the section and i have found an option called

Event Console Purging Maintenance (screenshot attached) There was an unchecked box which said 'Purge alerts by age'

Am I on the right track?

This does seem like the option that would solve this. I have checked it and chose to save changes.

I do think that SNMP discovery is also enabled.

8.jpg
andykn101's picture

That looks a likely option to me. I would have been tempted to do an immediate purge on it and see if the table shrank.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

md investigate's picture

Hi,

the question is for me: why is snmp discovery enabled? Do you need it? I'm not sure if this purging would resolve the problem. Just have a try and schedule it asap.

Regards

network101's picture

I do have a network descovery tasks setup that will perform a scan each week for any new devices.

I do a ping sweep and snmp discovery.

I've not found it that useful. it would have been nice to be able to group all the computers/routers/switches in one filter for a subnet but I never found out how to acchieve this.

Also get e-mailed when snmp noticed a device was offline, i never found where to do this either.

Normally I just use the computers section to filter pc's by subnet at each site.

9.jpg
network101's picture

This is the result of the Event console purging maintenance. it has helped slightly but not really much of a dent.

10.jpg
andykn101's picture

Are you sure it's finished purging, I'd expect getting 32 million rows down to 50,000 would take hours, not minutes.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

network101's picture

Hi yes it's slowly getting smaller I think it maybe could take days.

What's the best way to reclame the disk space on the sql server?

Thanks.

andykn101's picture

You can use the dbcc shrinkdatabase command but this can cause index and file fragmentation and should only be done as part of a planned SQL maintenance by a qualified SQL Admin who understands all the ramifications.

Authorised Symantec Consultant (ASC) with Endpoint Management Limited, an Authorised Symantec Delivery Provider based in the UK.

Connect Etiquette: Please "Mark as Solution" posts that fix your problem.

md investigate's picture

Hi,

sounds great.

For sql maintenance have a look here: http://www.symantec.com/docs/HOWTO8589

Regards

network101's picture

Thanks, I already have this one setup, its picture 6 on the first post of this thread, i was just wondering if there is anything else for database tuning/free space that I haven't got.

mortenleth's picture

Hey Network

I am not 100% sure that maintenance task actually shrinks your database file.
What you could do is run the maintenance task, and then have a look of the database to see how much free space you have.
Otherwise you have to do it manually, and the way to do this is this:
http://technet.microsoft.com/en-us/library/ms18903...

Kind Regards
Morten Leth

network101's picture

Thanks for your help with this motenleth, andykn101, md investigate

Im going to shrink the database next week.

network101's picture

For those interested on the results of the purging. Here is the result.

Later on this week ill shrink the DB size, there is a backup so I won't loose all in worst case.

12a.jpg
network101's picture

Hello. Thanks everyone for your assistance, this is now resolved.

mortenleth thanks for the shrink database link this worked great. The database is now a manageable size at 13gb.

 

14.jpg