vCenter MS SQL database won't shrink

[markokobal]

Hi,

I've spent all day on this topic. I've read all the KB's about scripts for purging old data and shrinking databases, but my database is still thick. I have vCenter 4.1 with database MS SQL Server 2008. I have 3 hosts with about 15 vm's; the vCenter calculator estimates DB size about 0.5 GB.

My database was 3.0 GB. I've run the purge script (with delete parameter = 1 and cutoff date 10 days - that should delete any records, older than 10 days). The script runs fine, deletes a bunch of records. Then I shrink database with 0% leaving space, but my database is still at 2.5 GB. What the hell is in it and how can I get it out? I also did count number of rows for all tables (exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''?'' as table_name from ?') and didn't find any outstanding number.

Please, advice.

[Troy_Clavell]

have you set your recovery mode to simple and also checked on any transaction logs?

[markokobal]

Hi,

Yes, I have set recovery mode to simple. And my transaction logs are in separate file. The VCDB.mdf is of size 2.5 GB and the VCDB_log.ldf is only 6 MB. I want to shring the 2.5 GB VCDB.mdf.

Any other insight?

[Troy_Clavell]

I'm not 100% sure what you have or have not done, but the below guides are useful to us

http://www.vmware.com/files/pdf/vc_microsoft_sql_server.pdf

http://kb.vmware.com/kb/1003990

http://kb.vmware.com/kb/1036738

[kjb007]

What does SQL Studio say you can shrink the db to?  I've had issues with SQL not releasing space before as well.

Sometimes having to resort to shutting down vcenter, restart sql, running the shrink again, and then bringing vcenter back up.

-KjB

[markokobal]

Hi,

Well, I did all of the following:

- I've run the purge script (with delete parameter = 1 and cutoff date 10  days - that should delete any records, older than 10 days)

- I shrank database with 0%  leaving space

- I set recovery mode to simple

- I did indexdefrag (http://kb.vmware.com/kb/1003990)

- I've restarted MS SQL server, ran all that again

My database is still 2.5 GB ... crap, I've run out of ideas.

[Troy_Clavell]

you may also want to check your database retention policy and logging options in vCenter.... Administration--vCenter Server settings.

[markokobal]

Hi,

Yes, I've set 30 days for tasks+events and Warning+Errors logging only ... but I guess this kind of functions doesn't actually produce such an amount of data in the database...

[Troy_Clavell]

I'm tapped then... Maybe you've simply gotten the DB instance as small as it can get. :smileyconfused:

Did you try kjb007 suggestion?

[kjb007 · Accepted solution]

When my db got large, the bulk of it was task and event data, 2nd was perf data.  You should be able to open your db, and then check the event/tasks tables to see how much space they are taking up on disk.

-KjB

[markokobal]

Hi,

The table VPX_TASK shows 0.594 MB and woila, the tables (with indexes) VPX_EVENT and VPX_EVENT_ARG takes up more than 2 GB of space ... I guess vCenter does not immediatly take into account the settings from database retention policy (I've set to keep task&events for 30 days only) ... I'll wait a little bit to see when will vCenter purge old record from VPX_EVENT and VPX_EVENT_ARG ...

I found this great script here: http://www.dbforums.com/microsoft-sql-server/1003535-query-showing-tablesize-mb.html it prints out an really nice report on each table space usage, row count, etc.

[RParker]

I've spent all day on this topic. I've read all the KB's about scripts for purging old data and shrinking databases, but my database is still thick. I have vCenter 4.1 with database MS SQL Server 2008. I have 3 hosts with about 15 vm's; the vCenter calculator estimates DB size about 0.5 GB.

That is an ESTIMATE, and 3GB is VERY small.. you shouldn't be worried about SQL sizes that small, our database is almost 200GB (40 hosts, 1000 VM's).. by comparison.

almost any SQL database you create is going to grow with any substantial use, I think you are trying to sqeeze blood from a turnip..

What's wrong with a 3GB database, you need to make it 500 Meg just because VM Ware documentation says it SHOULD be that small?  That's only a guideline...

[kjb007]

Yeah, that purge job generally will take some time to complete.  For me, it took a weekend, because the purge task didn't want to eat up all the cpu.  You can go into the SQL, and under sql server agent, you can manually run the purge job until it runs clean, and comes back quick.

-KjB

[markokobal]

Hi,

RParker, of course it is not my goal to get database as small as it can get ... this is just an test environment and my goal was to understand the structure of data to be able optimize size & performance of the vCenter database when 1000s of VMs are handled by vCenter ... I guess I have now all the parameters, thanks all for help!

To get a summary of shrinking:

- use the script here: http://www.dbforums.com/microsoft-sql-server/1003535-query-showing-tablesize-mb.html to prints out an really nice report on each table space usage;

- to delete statistics history: run the purge script, found here: http://kb.vmware.com/kb/1025914;

- to delete events and tasks history: set correct database retention policy in vCenter: Administration -> vCenter Server settings (or manually delete records from tables VPX_TASK, VPX_EVENT and VPX_EVENT_ARG;

- finally shrink database (either by executing DBCC SHRINKDATABASE (VCDB); or Database Properties / Tasks / Shrink / Database)

- optionally you may want to defrag indexes, as described here: indexdefrag (http://kb.vmware.com/kb/1003990)

Again, thanks all for help!