Saturday, June 29, 2013

ESXi/vCenter find on whitch host a VM is running

Some times we need to know on which host is running a VM.

Is always a pain to discover this when you don't have your vCenter running.
You can check the VM logs etc.,(looking for something similar to 2012-10-10T02:04:01.999Z| vmx| Hostname=name_of_host)

But for me the best option is just run a query directly in the vCenter DB.

Just run this simple query in the vCenter DB(should be VIM_VCDB, or just VCDB)

SELECT vpxv_vms.vmid, vpxv_vms.NAME, vpxv_vms.hostid, vpxv_hosts.NAME
FROM vpxv_vms
JOIN vpxv_hosts on VPXV_VMS.HOSTID = VPXV_HOSTS.HOSTID
WHERE (
(vpxv_hosts.hostid = vpxv_vms.hostid)
AND (vpxv_vms.NAME = 'virtual_machine_name')
)

This will give you in a simple line the VM and the host that is running.

Hope this can help.

Luciano Patrão

Note: Check VMware KB2004187 for more details

Wednesday, June 26, 2013

vCenter not starting - VCDB database ‘PRIMARY’ filegroup is full

Today I had some issues with my vCenter(5.0.x). The database was not working and even vCenter services were running no connections was possible to the DB(SQL 2008 Enterprise SP1).


After checking the logs(vpx) from vCenter I found some errors

"[SQL Server]Could not allocate space for object 'dbo.VPX_HOST_VM_CONFIG_OPTION'.'PK_VPX_HOST_VM_CONFIG_OPTION' in database 'VCDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."

Note: Logs of vCenter are located at \ProgramData\VMware\VMware VirtualCenter\Logs
Check also KB 1021804 for vCenter logs location.

This means all connections to DB were dropped and that the ‘PRIMARY’ filegroup is full and it consumes all my disk space where DBs were allocated(in this case was d:)

I had lot of old trash information in the DB, need to purge most of the information and also shrink the DB to fix this issues. Also my vCenter DB was growing to much(was 35Gb).

To clean the DB we need to run some SQL scripts.

These Script can be found in VMware KB 1025914

Using SQL Management Studio connect to your DB Server(local or remote).

Note: Before running the script check if you have the right DB connected(standard DB name for vCenter is VCDB or VIM_VCDB)

Before you run the script you can change the days of data you want to keep. The standard is 180 days, in my case I just want the last 30 days, the rest was to delete.
Change here: SET @CUTOFF_DATE = GETUTCDATE()-30 (number of days that you want to have data in your DB)

Tasks:

Stop all vCenter services
Create a full backup of your DB
Run the script with the flag @DELETE_DATA = 0 (this is the default). This only count the rows on the DB, will not delete any data.

After checking all the information that was collected in the previous step, just change the @DELETE_DATA to 1 and run the script to delete the data.

These task can take very long time. It depends on the size of your DB and the amount of information that you have. My task was around 3h.

After you purge all old data, you need to shrink your vCenter DB. In you SQL Management Studio go to Object Explorer and right click in your DB(this case VCDB), tasks and then Shrink database.

After this you can restart you vCenter services and check if everything is working smoothly.

Hope this can help.

Luciano Patrão

Blog is reactivated

Today I decided to reactive this blog(never was really activated), but after today will try to add some articles. Blog is reactivated after today. Luciano Patrão