Disk space issue in VMware VCenter 5.1 SQL Express Database ?

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 ?
LVL 9
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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!
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
I've got 21 ESXi 5.1 U3 hosts and approximately 200 VMs running.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Logging
This is my logging details, so if I change it all to 1 would there be significant disk space reclaimed automatically ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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!
0
 
Senior IT System EngineerIT ProfessionalAuthor 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 ?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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 ?
0
 
Senior IT System EngineerIT ProfessionalAuthor 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.
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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.
0
 
Senior IT System EngineerIT ProfessionalAuthor 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.
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I would do a full backup! (not a snapshot) because performance is terrible on a snapshot!
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
ok, so does the second script can delete the old tasks & events in the past 6 months ?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
it drops them all.

what did the first script produce ?

did it show these tables to be the cause ?
0
 
Senior IT System EngineerIT ProfessionalAuthor 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
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
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.
0
 
Senior IT System EngineerIT ProfessionalAuthor 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 ?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
As I thought those are large values!

It will zero them.
0
 
Senior IT System EngineerIT ProfessionalAuthor Commented:
Ok if it is going to zero them the task and events will all be empty right ?

And also the statistics & graphs.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.