Tuesday, July 16, 2013

VMware: vCenter find who deleted a VM

Today I need to check who deleted a Virtual Machine from our vCenter. Looking this task in logs can be painful and spend lot of time, so the best way to check this is in the vCenter DB.

Just connect to vCenter DB(in our case was VCDB and using SQL Server Management Studio) and run a small query and you will have all the information.



Where the VMNAME is the name of your Virtual Machine that was deleted.

If you don't know the full name of the VM, you can just use wildcards(AND VM_NAME LIKE 'VM%'). This will show all results from all Virtual Machines that start with 'VM'.
You can use many different wildcards to look for the right result. Please check Microsoft KBQ98434 how to use wildcards.

Using VPX_EVENT you can query lot of events/tasks that was performed on VMs/Hosts.


vim.event.VmPoweredOffEvent - Virtual Machines that were Poweroff(without using Guest Powerdown)

Hope this can help.

Luciano Patrão

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
(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)


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