Disk space issue in VMware VCenter 5.1 SQL Express Database ?

Senior IT System Engineer
Senior IT System Engineer used Ask the Experts™
on
People,

I'm having a problem with the disk space usage my Windows VMware VCenter 5.1 U1 which is still running SQL Express v10 in the same Windows VM.

How to reduce the disk space usage on the SQL Express database ?

Database: 39.9 GB
Location: D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

Logs: 93 MB
Location: D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

Is there any way to reduce the disk space usage on this SQL Express database without causing any harm to the production server access and uptime ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
Yes, there is, remove the events and tasks.

Change the Logging Detail in vCenter Server.

How many VMs and Hosts do you have ?

ESXi 5.1 logs far more information, than 5.0 ever did!

Author

Commented:
I've got 21 ESXi 5.1 U3 hosts and approximately 200 VMs running.

Author

Commented:
Logging
This is my logging details, so if I change it all to 1 would there be significant disk space reclaimed automatically ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
21 ESXi 5.1 U3 hosts and approximately 200 VMs running.

WOW!

That's the issue, TOO MANY for SQL Express.

You need to upgrade to the full version of SQL.

or switch to the vCenter Appliance!

SQL Express is only suitable for small installations, of a few hosts e.g. 2-3 with 50 VMs!

Author

Commented:
Yeah, I know. but in this case is it possible to reduce the logging level and then somehow reclaim the disk space while I'm finding some SQL Server 2008 / 2012 Std. stand alone to amnually migrate the DB ?
Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
No, you will not be able to reclaim the disk space, without clearing the data in the tables, and then doing an SQL shrink

The issue will then return.

I can write an SQL procedure for you to try, are you an SQL whizz ?

Author

Commented:
Hi Andrew,

Thanks for the help, yes please, I'm not a DBA, but yes, I'd love to try running that script you have to reduce the disk space.

My understanding is that the database file (MDF) will contains white space after the data is cleared up.
Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
it's late here in the UK, I'll upload two scripts in the morning here in the UK (GMT+1) tomorrow.

which will clear the tables, in two of the tables I think could be causing you issues.

I'll ask you to check the tables before running the SQL procedure.

Author

Commented:
yes, that'd be great idea.

Many thanks for the update Andrew. it is GMT +10 here in Australia.

So therefore, I will take a VCenter VM snapshot and SQL Database backup before running this SQL script to make sure that I can roll back in the event there is a failure or unwanted effects.
Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
I would do a full backup! (not a snapshot) because performance is terrible on a snapshot!
VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017
Commented:
Sorry, I forgot about this...

okay here goes...

Check the following tables...in the database VIM_VCDB

There are two scripts here, the first one will show the row and total space used in the table.

SELECT [Table Name],
(SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM 
                (
                SELECT  QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
                                CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
                FROM    sysindexes i (NOLOCK)
                                                INNER JOIN
                                sysobjects o (NOLOCK)
                                                ON
                                i.id = o.id AND
                                ((o.type IN ('U', 'S')) OR o.type = 'U') AND
                                (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0)
                WHERE indid IN (0, 1, 255)
                GROUP BY           QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
                
                ) as a
ORDER BY            [Total space used (MB)] DESC 

Open in new window


the second script here, will remove Tasks and Events from the Db

alter table VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT, FK_VPX_EVENT_ARG_REF_ENTITY 
alter table VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT

truncate table VPX_TASK
truncate table VPX_ENTITY_LAST_EVENT
truncate table VPX_EVENT
truncate table VPX_EVENT_ARG

alter table VPX_EVENT_ARG add
constraint FK_VPX_EVENT_ARG_REF_EVENT foreign key(EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade, 
constraint FK_VPX_EVENT_ARG_REF_ENTITY foreign key (OBJ_TYPE) references VPX_OBJECT_TYPE (ID)

alter table VPX_ENTITY_LAST_EVENT add
constraint FK_VPX_LAST_EVENT_EVENT foreign key(LAST_EVENT_ID) references VPX_EVENT (EVENT_ID) on delete cascade

Open in new window


You run these scripts "as is", and your responsibility.

I would run the first script and check, if the tasks and events are large... and these are the tables causing the excessive space issue.

Author

Commented:
ok, so does the second script can delete the old tasks & events in the past 6 months ?
Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
it drops them all.

what did the first script produce ?

did it show these tables to be the cause ?

Author

Commented:
I haven't executed it yet since today is public holiday here.

So, I've found this script:http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1025914

Can I execute it to truncate the data based on specific custom date ?

For Example: to retain the data in the past 180 days:

SET @CUTOFF_DATE = GETUTCDATE()-180

Open in new window

VCDB-table-cleanup-MSSQL-V4.X.sql
Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
you could create a script or change a script.

remember events and tasks are only visible from vCentrer for 24 hours, to extract further info, you would have to write a script to extract the information, or use scripts and powershell.

Author

Commented:
Hi Andrew,

Here's the result of the first script:
First Script result
So by running your second script will it drop them all to 0 ? or you leave some data to be there in the database ?
Andrew Hancock (VMware vExpert / EE Fellow)VMware and Virtualization Consultant
Fellow 2018
Expert of the Year 2017

Commented:
As I thought those are large values!

It will zero them.

Author

Commented:
Ok if it is going to zero them the task and events will all be empty right ?

And also the statistics & graphs.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial