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
Logs: 93 MB
Location: D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
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 ?
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
Logs: 93 MB
Location: D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
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 ?
ASKER
I've got 21 ESXi 5.1 U3 hosts and approximately 200 VMs running.
ASKER
This is my logging details, so if I change it all to 1 would there be significant disk space reclaimed automatically ?
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!
ASKER
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 ?
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 ?
The issue will then return.
I can write an SQL procedure for you to try, are you an SQL whizz ?
ASKER
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.
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.
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.
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.
ASKER
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.
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.
I would do a full backup! (not a snapshot) because performance is terrible on a snapshot!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, so does the second script can delete the old tasks & events in the past 6 months ?
it drops them all.
what did the first script produce ?
did it show these tables to be the cause ?
what did the first script produce ?
did it show these tables to be the cause ?
ASKER
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:
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
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.
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.
ASKER
Hi Andrew,
Here's the result of the first script:
So by running your second script will it drop them all to 0 ? or you leave some data to be there in the database ?
Here's the result of the first script:
So by running your second script will it drop them all to 0 ? or you leave some data to be there in the database ?
As I thought those are large values!
It will zero them.
It will zero them.
ASKER
Ok if it is going to zero them the task and events will all be empty right ?
And also the statistics & graphs.
And also the statistics & graphs.
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!