VMware Cloud Community
Jae_Ellers
Virtuoso
Virtuoso
Jump to solution

beginning of the end? VC db growing 100+ MB/day

Hi folks,

My VC db was stable around 2.5 G for a long time. Since I've implemented a couple of maintenance plans I've seen it start to grow around 100 MB each time the backup runs.

I have not increased the statistics level, it is set at Level 1 per VMware Support. I have 16 thread limit for the collection tasks.

Db is MSSQL 2000 SP4 on the local system, Windows Server 2003SP1.

VC is 2.0.1u2.

Something is happening here. What it is ain't exactly clear. I'll open an SR Monday, but the ham-handed level I's usually just want to reinstall or start blowing statistics away willy-nilly. I've fixed any problems myself in the past, so any help would be appreciated.

Any ideas?

-=-=-=-=-=-=-=-=-=-=-=-=-=-=- http://blog.mr-vm.com http://www.vmprofessional.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Reply
0 Kudos
1 Solution

Accepted Solutions
esiebert7625
Immortal
Immortal
Jump to solution

You'd be surprised how many rows are in events and tasks and there is no way to purge it through VC which is why I came up with SQL code to do it manually. These will grow forever unless manually purged. Statistics will grow for a year and then should be fairly fixed unless you add alot more hosts and VM's. I would adjust the default samples if you really do not need that much history to help keep the size down. When you do adjust it it does wipe out all the previous stats for that area.

http://www.vmware-land.com/Vmware_Tips.html#VC4

http://www.vmware-land.com/Vmware_Tips.html#VC5

• Past Day – 5 minutes per sample, 288 total samples

• Past Week – 15 minutes per sample, 672 total samples

• Past Month - 60 minutes per sample, 720 total samples

• Past Year – 1440 minutes per sample, 365 total samples

If you do purge the db a bit you can shrink it using the below technique.

How can I reclaim space in my SQL database after I have purged old data from it?

• If you have deleted old task/events or perfomance data by default your database will not automatically shrink to recover this free space and reduce the physical size of your VC database file on your host OS disk. You can set your VC database to automatically shrink by loading SQL Server Enterprise Manager, selecting your VC database, then Properties and on the Options tabs you can check the “Auto Shrink” option. Alternately you can manually shrink the database by doing the below steps. This procedure will shrink both the database (mdf file) and transaction log (ldf file).

o Load the SQL Query Analyzer on the SQL Server and login with your virtualcenter database credentials

o Type the following in the query window

 DBCC Shrinkdatabase ( database name, target free space percent ) ie. DBCC Shrinkdatabase ( virtualcenter, 10 )

o This will shrink your database down and reduce the physical file size and leave 10% room for further growth. Your database will auto grow onit’s own once it uses up the 10% growth space.

View solution in original post

Reply
0 Kudos
6 Replies
esiebert7625
Immortal
Immortal
Jump to solution

Is it the db or the log that is growing? You can find out more info by using the below procedures. You could also make a daily note of the number of rows in each table to see what is growing the most. Specifically check vpx_hist_stat, vpx_events and vpx_tasks. I have alot of db info documented here. I really do not think you will get a good response from support. They will tell you that they do not support the database component of VC. This is what they told me when I asked how to purge old tasks and events.

http://www.vmware-land.com/Vmware_Tips.html#VC6

How can I find out information about my VirtualCenter SQL database?

• You can used the sp_helpfile stored procedure to get general information on your VC SQL database.

o Load the SQL Query Analyzer on the SQL Server and login with your virtualcenter database credentials

o Type the following in the query window

 Use ie. virtualcenter

 EXEC Sp_spaceused ‘table name’ ie. vpx_hist_stat

o Press F5 or the Execute Query icon

o The results window will return the table name, number of rows (records), total amount of space reserved for the table, total amount of space used by the data, total amount of space used by indexes and the total amount of space reserved but not yet used.

Jae_Ellers
Virtuoso
Virtuoso
Jump to solution

It's the db. Both the size of the mdf and the backup (.bak) grows each day. Thanks for the tips. I'll put together a query to pull all the table stats.

I've been sorely disappointed whenever it comes to VC database issues and VMware support. Couple months ago I was down for 36 hrs and the techs had me barking up 3 wrong trees in a row over 12 hours. I came in the next morning and read the logs and fixed it in 10 minutes. By then they'd already blown away my statistics for no particular reason.

Getting my $20k quote for support ready again for next year. Don't think it's worthwhile, but it's insurance, I guess.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=- http://blog.mr-vm.com http://www.vmprofessional.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Reply
0 Kudos
esiebert7625
Immortal
Immortal
Jump to solution

You'd be surprised how many rows are in events and tasks and there is no way to purge it through VC which is why I came up with SQL code to do it manually. These will grow forever unless manually purged. Statistics will grow for a year and then should be fairly fixed unless you add alot more hosts and VM's. I would adjust the default samples if you really do not need that much history to help keep the size down. When you do adjust it it does wipe out all the previous stats for that area.

http://www.vmware-land.com/Vmware_Tips.html#VC4

http://www.vmware-land.com/Vmware_Tips.html#VC5

• Past Day – 5 minutes per sample, 288 total samples

• Past Week – 15 minutes per sample, 672 total samples

• Past Month - 60 minutes per sample, 720 total samples

• Past Year – 1440 minutes per sample, 365 total samples

If you do purge the db a bit you can shrink it using the below technique.

How can I reclaim space in my SQL database after I have purged old data from it?

• If you have deleted old task/events or perfomance data by default your database will not automatically shrink to recover this free space and reduce the physical size of your VC database file on your host OS disk. You can set your VC database to automatically shrink by loading SQL Server Enterprise Manager, selecting your VC database, then Properties and on the Options tabs you can check the “Auto Shrink” option. Alternately you can manually shrink the database by doing the below steps. This procedure will shrink both the database (mdf file) and transaction log (ldf file).

o Load the SQL Query Analyzer on the SQL Server and login with your virtualcenter database credentials

o Type the following in the query window

 DBCC Shrinkdatabase ( database name, target free space percent ) ie. DBCC Shrinkdatabase ( virtualcenter, 10 )

o This will shrink your database down and reduce the physical file size and leave 10% room for further growth. Your database will auto grow onit’s own once it uses up the 10% growth space.

Reply
0 Kudos
Jae_Ellers
Virtuoso
Virtuoso
Jump to solution

Good points. I'm worrying because it's been stable at 2-2.5 Gb for literally years now. And all of a sudden it's taken off. Don't like unexplained things, especially in the database.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=- http://blog.mr-vm.com http://www.vmprofessional.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Reply
0 Kudos
Jae_Ellers
Virtuoso
Virtuoso
Jump to solution

VMware support has responded with an updated sp for VC 2.0.1 update 2.

http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&externalId=1001420

I also found that a VMware support tech had renamed a couple of the vpx_stat_rollup sps. Think that had anything to do with my db growth?

-=-=-=-=-=-=-=-=-=-=-=-=-=-=- http://blog.mr-vm.com http://www.vmprofessional.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Reply
0 Kudos
esiebert7625
Immortal
Immortal
Jump to solution

Thanks, good to know. Added it to my link collection...