Near-Continuous replication and database growth

[mlinders]

Hi all, just putting this one here to get some additional insights. It touches a mix of VMware and Veeam.

We just implemented a near-continuous replication solution for one of our clients and have noticed some things that I would have expected to show up in the manual. The setup is fairly simple, there is an HQ where all the production VM's are running. Besides that we have a DR site. On this site we have Veeam B&R running. We also have an install of Veeam B&R in the HQ site for proxy functionality and local backup if neccessary. Our VM's are located on SAN and everything is working prety fine.

Some important VM's (about 4) are replicated near-continuous from HQ to DR-Site. The replication jobs are initiated from our DR site which connects to the vCenter server in the HQ. What we noticed is that the vCenter database is quickly filling up with Events and Tasks rows in de table. We limited the retention for these events to 3 days, but even that was too large for the Express SQL database that was underneath. The problem is that vCenter tries to run a cleanup every six hours of Events and Tasks using a stored procedure. The massive amount of events and tasks would cause the vCenter database to fill up it's transaction log (DB mode is simple, but still uses Tlog's for long running queries). This would break the near-continous replication and causes Veeam temporary snapshots nested inside Veeam temporary snapshots. After changing the events and tasks retention to 1 day and truncating the tables with help of VMware support it seems stable.

We thought we were out of the woods and all was peachy again, unfortunatly we replicate to a vCenter server in te DR site. So this DR site is where the _replica VM's are residing with some restore points for each VM (28-points per VM). What we noticed is that one table in the vCenter database (VPX_INT_ARRAY) keeps growing steadily with 50.000 rows a day. I have been told by VMware that this table cannot be truncated and that it is used to keep VM information stored. We thought offcourse that it was due to the fact we were specifying the vCenter as target for our replica's, so we changed this to target the ESX(i) host instead of the vCenter server, but this changes nothing. vCenter still notices all the VM's being handled by the replication and starts growing again. The moment we stop replication, the table stops growing.

To make a long story short:
- Do you see this same massive growth in the vCenter database tables when using near-continuous replication? And how do you manage it?
- Have you come into the situation where events and tasks history (as low as 3 days) is causing transaction logs to fill up.

* It's a small environment with 3 hosts in the HQ and about 20 VM's)

# 00264477

[vcocaud]

Hi,

Same problem here.
Veeam 6.5, continuous replication of ~15VMs to same vCenter as source (different SAN).

vCenter Database is growing since 4GB limit (SQL 2005 Express) and vCenter crash ...

I've not found any solution for now.

[CaptainFred]

Hi,

We might use continuous replication in the future so when I read this I was a bit concerned. Forgive my ignorance but when you say "truncating the tables" do you mean using a SQL script to clear out entries? Or does this break everything?

Also for interest, how much data is changing at each replication run and how much is being transferred?

[Vitaliy S.]

Valentin, if the vCenter Server generates too much data and outgrows the max size of the database, you might either set a smaller retention period to our vCenter Server database or migrate this database to SQL Server 2008/2012 Express, this will give you 10 GBs limit for the database.

[mlinders]

Hi,

We might use continuous replication in the future so when I read this I was a bit concerned. Forgive my ignorance but when you say "truncating the tables" do you mean using a SQL script to clear out entries? Or does this break everything?

Also for interest, how much data is changing at each replication run and how much is being transferred?

Hi CaptainFred,

- With Truncating the tables i mean SQL script to clear out entries. Basically deleting database records. Mostly these were Tasks and Events entries.
- VMWare has instructed me that VPXINTARRAY should not be truncated as this might possibly break things. This has been growing steadily to well over 3 million records.
- Veeam Replication finishes a run about every 5 minutes. That is including the create and commit snapshot operation, you might want to test impact on your operation first, since the snapshot commit every five minutes might be quite disruptive to Tier 1 applications like Exchange for instance. We see that as a few seconds freeze in Exchange responsiveness every 5 minutes. (SSD has much less freeze/stun then SAS, SATA, SAN, etc.) There is very little data being transfered over the line since CBT keeps track of what has changed and there isnt that much of a change in 5 minutes.

Hope this helps.

[veremin]

Additionally, it might be worth putting into use new parallel processing functionality that is supposed to reduce the time it takes to process Exchange VM and, therefore, lower the time of snapshot commit operation. Thanks.

[CaptainFred]

Hi CaptainFred,

- With Truncating the tables i mean SQL script to clear out entries. Basically deleting database records. Mostly these were Tasks and Events entries.
- VMWare has instructed me that VPXINTARRAY should not be truncated as this might possibly break things. This has been growing steadily to well over 3 million records.
- Veeam Replication finishes a run about every 5 minutes. That is including the create and commit snapshot operation, you might want to test impact on your operation first, since the snapshot commit every five minutes might be quite disruptive to Tier 1 applications like Exchange for instance. We see that as a few seconds freeze in Exchange responsiveness every 5 minutes. (SSD has much less freeze/stun then SAS, SATA, SAN, etc.) There is very little data being transfered over the line since CBT keeps track of what has changed and there isnt that much of a change in 5 minutes.

Hope this helps.

Ah yes thanks

Additionally, it might be worth putting into use new parallel processing functionality that is supposed to reduce the time it takes to process Exchange VM and, therefore, lower the time of snapshot commit operation. Thanks.

What's that? How do we use it?

[veremin]

With the introduction of this option, VB&R can process multiple VMs and VM disks in parallel, this’s supposed to reduce backup window and increase backup performance rate.

You should go to the Menu -> Options -> Advanced -> Enable parallel processing. Though, please be aware that this option is likely to be helpful if your Exchange server has more than one virtual disk and, also, if you have enough resource to perform more than one concurrent task.

More information regarding it can be found in the corresponding User Guide (p.476). Thanks.

[mlinders]

Just got an update from VMware regarding VPX_INT_ARRAY, it seems the large number of records are due to huge number of orphan data records that exist in this table that are no longer referenced by any of their parent tables. Advice from VMware: Truncate the VPX_INT_ARRAY table, as vCenter will resync the info. We performed a truncate.

Veeam Backup & Replication will no longer replicate succesfully after truncating VPX_INT_ARRAY:
It looks like Veeam no longer knows the replica servers. We get errors like "The name <NAME>_replica already exists"

I restored the pre-truncate backup and Veeam starts replicating again without errors. This table has to do with mapping between Veeam and the replica virtual machines.

[lars@norstat.no]

Remember that SQL Express 2008 has the same 4 GB limit on database as 2005 version, but SQL 2012 has a 10GB limit. There is also a limit at 1 CPU socket OR 4 Cores so remember to choose 1 socket with 4 cores in systems settings for the Virtual machine used to run Vcenter and not the other way around. By far the biggest limitation on SQL Express is that it can only use 1GB of RAM.

Here is the script i used to clean out the old data from the VPX_INT_ARRAY table in the Vcenter database:

http://kb.vmware.com/selfservice/micros ... Id=2005333

After you have cleaned out the old entries run this script:

http://www.mssqltips.com/sqlservertip/1 ... databases/

This will rebuild all your tables and indexes, after you have done that, shrink the database and then it's VERY important to run the rebuild script again because otherwise your indexes will be fragmented. it will only take seconds but you need to take the database into single user mode for the first script, essentially taking the database down.

I also deleted all task logs and event logs from the database and set retention for 7 days, but i have heard people having to set it to one day when using Veeam.

In the end i reduced my Vcenter database from 50 GB to about 5 GB and everything is very fast now

[mlinders]

Hello Lars, we are using SQL 2008 R2 which has 10 GB limit. The KB from VMWare is about VPX_TEXT_ARRAY, not VPX_INT_ARRAY. Our database retention is already set for 1 day, because continuous replication floods the database with tasks and events. The problem is that the target vCenter server for replication jobs shows a steady growth of 10.000+ records per day in VPX_INT_ARRAY. The moment we stop replication, the growth stops. It's still under investigation by VMWare. I'll update this post as soon as we found out what is causing this.

[lars@norstat.no]

Of course, forgot about the R2 version

Sorry for posting the wrong link, i'm sure i also managed to clean out the VPX_INT_ARRAY, but i will come back to you when i can find the solution i used. That said, my other tables where also quite big so cleaning out the VPX_TEXT_ARRAY also gave me quite a boost in performance.

Also i have only found a solution to how to clean it out, but i would also like to find out how to stop it from growing.

[mlinders]

After some more testing and working with VMware we have come up with the following method for truncating VPX_INT_ARRAY.

* Disable any Veeam replication jobs you have running
* Log onto your vCenter server and stop the service "VMware VirtualCenter Server"
* Backup you vCenter Database (just to make sure, do not skip this step)

* Use the following T-SQL query on the vCenter database:

Code: Select all

truncate table vpx_int_array
select count(*) from VPX_INT_ARRAY

VPX_INT_ARRAY should return a value of: 0

* Log onto your vCenter server and start the service "VMware VirtualCenter Server"

If we would enable replication at this stage, the replication would fail with the following error:


We need to perform a host resync to populate the VPX_INT_ARRAY with information from the vSphere hosts. To do this:
* Connect to your vCenter server using the vSphere client.
* For every Host in your environment click "Disconnect" and "Connect" right after. This will repopulate VPX_INT_ARRAY.

* You can verify this with the previous T-SQL Query:

Code: Select all

select count(*) from VPX_INT_ARRAY

VPX_INT_ARRAY should return a value higher than: 0

If we would enable replication after the resync, replication will continue without issue:


This procedure will not stop your VPX_INT_ARRAY table from growing when using Continuous Replication, but it will give you the possibility to truncate the table when your SQL (Express) database is full due to the table size. Use at your own risk offcourse. We are still working with VMware to prevent the table from growing this large, as this is unexpected behaviour.

[Gostev]

Hi Lars, thank you very much for coming back and posting the detailed instructions for future readers. This has even caught an eye of our support folks

[mlinders]

Just received word back from VMware. Here is the official statement:

"The fix for this issue is currently in development for 5.1 Update 3 which is due for release approximation the end of this year. Due to the time frame I would like to suggest we archive this case. When 5.1 U3 is release can you upgrade your systems when possible. If the issue continues we can open a new case - referencing this one."

Regards,
Mark (That's not spelled Lars.... Gostev )

[dellock6]

Really nice *Mark*, thanks for the detailed informations.

Luca.

[Unison]

Just received word back from VMware. Here is the official statement:

"The fix for this issue is currently in development for 5.1 Update 3 which is due for release approximation the end of this year. Due to the time frame I would like to suggest we archive this case. When 5.1 U3 is release can you upgrade your systems when possible. If the issue continues we can open a new case - referencing this one."

Regards,
Mark (That's not spelled Lars.... Gostev )

Hey Mark,
With Update 3 for 5.1 being released recently, did that provide any relief for these problems or have you moved on to 5.5? Has this issue presented in 5.5 at all?
We are just starting to see these symptoms in our environment (massive trans log file growth, nested veeam temp images getting stuck causing huge vm performance issues - never seen these issues until last couple months) and are running 5.1.

[lars@norstat.no]

I can say that these issues are still present on vSphere 5.5 with the very latest updates as of today.

My Vcenter grinded to an absolute standstill the other day and i reduced the database of the Vcenter from 68 GB down to about 7-8 GB.

Have to do this at least every 3 monts, but the slow down is incremental.

We are running about 2400 migration every 24 hours.

[Unison]

Thats disappointing to hear
Its been difficult to find other reports of this - specially with others seeing the nested Veeam Temp Snapshots - do you know of others seeing this problem consistently?

We have not changed our replication setup, hosts, storage or network recently (not even any new vmware updates) which makes it hard to determine why this issue only just started happening here recently. For years this setup has run with no nested Veeam Temp Snapshots or vcentre database blowouts.

have you discovered any clues about the true cause of this or just given in to it and added it to your admin functions - clear the database regularly and watch for nested Veeam Temp Snapshots (got a vCLI script to run a couple times a day to help me capture the nested snapshots)?

Is it your database that is growing (.mdf) or is it your log.ldf file that is growing huge? It is my log.ldf that is growing huge....the transaction log....i am able to shrink that by 99% - so i am not sure why the transactions are not clearing out of here anymore considering it will allow me to do it manually....if the transaction was still running, i wouldn't think that the trans log could be reduced by 99%.

[mlinders]

Hello Unison,

Lars is correct, this issue is still appearing in vSphere 5.5. I do have to say that there has been some slight improvement on the growth. With the latest versions of vSphere 5.5 we only have to clear our database about twice a year. This time is obviously based on the amount of replica jobs you are running.

The issue we had was not transaction log growth, but database growth. Since we are using the express version the 10 GB limit was reached.

I have seen issues with transaction logs growing quite large on VMware databases. This started when the maximum size for the log was set too small. Basically what happened was a cleanup procedure in the database was causing the log to grow. This makes sense, since it's making transactions in the database. Because the transaction log was full the changes were rolled back. This left the transaction log huge, and the database was unable to maintain itself, so the problem got worse over time.

If I remember it correctly we did 2 things:
1) Since we didn't have the SQL Agent tasks (SQL Express) we used Windows Scheduled tasks to cleanup the VMware database
2) We set the transaction log size to maximum 20 GB, so there is enough room for the DB maintenance to succeed.

Regards,
Mark

[Unison]

Hi Mark,
Thanks for confirming about the problem in 5.5 - i wont consider that the hail mary to this problem anymore

I have given in to shrinking the database/files again and everything is back to being small. I have also disabled our replication job completely - instead bumping up the incrementals on normal jobs to boost our recovery point objective........i am going to sit on this config for several months and see what happens to the database - see if it was the replication job blowing out the trans/database.

I think that others have probably run into vcenter services stopping because of the trans log hitting a max set size (when trying to run clean ups and then having to roll back - making no progress) - i didnt have that here as the trans log is set to unlimited growth.

Will keep an eye on this and see what happens from here.

[larry]

Sorry for not sharing long ago but... After having it happen at the worst time again, which is ever, I fixed with below. I used for a year plus now.

I run a weekly scheduled task "VL-Log-Shink"

Code: Select all

task runs C:\SQLCommands\swhinklog.bat
starts in C:\SQLCommands
run with highest privileged, not logged in. - local admin account.

swhinklog.bat contains one line
sqlcmd -S GR-VC\VIM_SQLEXP -i C:\SQLCommands\ShinkLog.sql >C:\SQLCommands\log.txt

ShinkLog.sql has 4 lines
USE VIM_VCDB
ALTER DATABASE [VIM_VCDB] SET RECOVERY SIMPLE;
DBCC SHRINKFILE(VIM_VCDB_log);
ALTER DATABASE [VIM_VCDB] SET RECOVERY FULL;

Never had an issue again.

To run on demand you must right click bat file and run as administrator

[Unison]

Hey Larry,
thanks for posting - good solution to keep the log file under control (essentially its doing the same as using the GUI to shrink just the log file). Might deploy this 'automatic' method if my log continues to grow even after i have stopped replication.

[dellock6]

vSphere 5.1 update 3 has been released few weeks ago, I've searched on release notes but I didn't find any reference to VPX_INT_ARRAY, Mark by any chance have you checked if this updates has fixed this issue as told by VMware when you opened the support case?

[mlinders]

Hello Luca,

No, I have not tested if the upgrade fixes the issue. We moved to vSphere 5.5 on all instances we are running. So there was no need for it. But if I had to guess... the problem will probably be still there even after the upgrade. But as I said, that's just a guess.

Regards,
Mark

[gerry_the_hat]

Hi Folks,

Seems that the Veeam support is not that aware of this issue. Had some cases open related to "An existing connection was forcibly closed", which then seems to be related to this unfixed VMware bug. Can someone please help and force VMware to relase a fix?

@Anton:
Can you help?

Here is what I've just sent to Veeam support (case 01207942):
A small update from my side: Seems that we ran into a know issue on VMware (see VMware case 15818171111). This is related to Veeam replication used over a long periode of time. What VMware processes in a wrong manner is the amount of logs etc. in the VMware SQL database. Some tables (VPX_INT_ARRAY in particular) grow over time and decreases the overall vCenter performance dramatically. This leads to timeouts and error messages like "An existing connection was forcibly closed". The workaround is to set up a database cleanup job at a regular basis. It might be the case that replication is used by other customers in short intervals as well, so this issue could possible affect quite some Veeam users. Therefore I'd like to suggest publishing a knowledge base article. You may close this case now. Thanks Gerd


Cheers
Gerd

[mlinders]

I was amazed I still got a reply on this original post. Seemingly this is a still ongoing issue. We made a "rough" powershell script that does the cleaning for us.

Basically it does the following:
- Stop vCenter services
- Back-up SQL Database
- Cleanout the vCenter database tables
- Start vCenter services
- Disconnect and reconnect the Hosts into your vCenter server (resync)
- Perform shrink of the Database

Note: Total duration of the actions might take an hour depending on your database. Escpecially the database cleanup can take a long time. I have seen occurances in which it took an hour to clean up.

You might want to test this first on your own environment. Should work on 5.1 and 5.5 installations. Not tested against 6.0.
Disclaimer: USE AT YOUR OWN RISK

Code: Select all

# Pre's: - Make sure you have free space at least the same amount as the current VMware database size.
# - Set your maximum transaction log size to 20 GB
# - Configure the variables section
###

# Checking Elevated powershell rights
$isAdmin = ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
 if (!$IsAdmin)
 {
  Clear-Host
  WRITE-HOST "This script needs to be run Elevated (run as Administrator), exiting now" -foregroundcolor "red"
  exit
 }

Clear-host

# Variables
$vcserver = "<vCENTER_HOSTNAME>"# e.a. vcsserver
$username = "<vCENTER_USERNAME>"    # e.a. domain\administrator
$password = "<vCENTER_PASSWORD>"# e.a. password
$SQLInstance = "<SQL_INSTANCENAMEFQDN>"# e.a. VCSSERVER\VIM_SQLEXP
$Instance = "<SQL_INSTANCENAME>"# e.a. VIM_SQLEXP
$vCenterDB = "<vCENTER_DATABASE>"# e.a. VIM_VCDB
$backupfile = "<DBBACKUP_LOCATION>"# e.a. E:\SQLBackups\VMware vCenter\VIMVCDB_Pre-cleanup.bak"

# Load SnapIns
WRITE-HOST "-------------------- LOAD SNAPINS --------------------"
$snapins = @("SqlServerCmdletSnapin100", "SqlServerProviderSnapin100", "VMware.VimAutomation.Core")
foreach ($snapin in $snapins)
{
    if ( (Get-PSSnapin -Name $snapin -Registered -ErrorAction SilentlyContinue) -eq $null )
    {
        WRITE-HOST "Mandatory snapin: '$snapin' is not installed" -foregroundcolor "red"
        WRITE-HOST "ERROR: Snapin not installed" -foregroundcolor "red"
        WRITE-HOST "How to resolve: Install Snapin and rerun script"
exit
    }
    elseif ( (Get-PSSnapin -Name $snapin -ErrorAction SilentlyContinue) -eq $null )
    {
        WRITE-HOST "Mandatory snapin: '$snapin' is not loaded"
        WRITE-HOST "Now loading snapin: '$snapin'"
        Add-PsSnapin $snapin
if ( (Get-PSSnapin -Name $snapin -ErrorAction SilentlyContinue) -eq $null )
{
WRITE-HOST "ERROR: Unable to load snapin: '$snapin'" -foregroundcolor "red"
exit
}
else
{
WRITE-HOST "'$snapin' succesfully loaded" -Foregroundcolor "green"
}
    }
    else
    {
WRITE-HOST "'$snapin' already loaded, skipping..." -Foregroundcolor "green"
    }
}


WRITE-HOST "-------------------- Stopping Services --------------------"
# Stop vCenter Server
$services = @("VMware VirtualCenter Management Webservices", "Vmware VirtualCenter Server")
foreach ($service in $services)
{
    try
    {
WRITE-HOST "Stopping Service: $service"
WRITE-HOST "Please wait, this can take a few minutes"
        Stop-Service -displayname $service -Force -ErrorAction Stop
WRITE-HOST "$service Succesfully stopped" -Foregroundcolor "green"
WRITE-HOST ""
    }
    catch
    {
WRITE-HOST "Error stopping service: $service"
WRITE-HOST "$_.Exception.Message" -foregroundcolor "red"
WRITE-HOST "Exit script due to previous error" -foregroundcolor "red"
exit
    }
}



# Backup the vCenter Database
WRITE-HOST "-------------------- Backup SQL Database --------------------"
$sqlquery="BACKUP DATABASE $vCenterDB TO DISK=N'$backupfile' WITH INIT, STATS=5"
try 
{
    WRITE-HOST "Starting Backup of SQL Database"
    Invoke-SQLcmd -ServerInstance $SQLInstance -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop' -verbose
    WRITE-HOST "'$sqlquery' completed succesfully" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error)
}
WRITE-HOST ""



# Waiting x seconds for DB to recover from backup
$delay=5
WRITE-HOST "-------------------- $delay Second delay --------------------"
Start-sleep -s $delay
WRITE-HOST "Waited for $delay second(s), continue running script"
WRITE-HOST ""



# Cleanup the vCenter Database
WRITE-HOST "-------------------- Cleanup SQL Database --------------------"
$sqlquery="TRUNCATE TABLE VPX_INT_ARRAY"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="alter table VPX_EVENT_ARG drop constraint FK_VPX_EVENT_ARG_REF_EVENT, FK_VPX_EVENT_ARG_REF_ENTITY "
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="alter table VPX_ENTITY_LAST_EVENT drop constraint FK_VPX_LAST_EVENT_EVENT"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="truncate table VPX_TASK"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="truncate table VPX_ENTITY_LAST_EVENT"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="truncate table VPX_EVENT"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="truncate table VPX_EVENT_ARG"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="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)"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="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"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="alter database $vCenterDB set single_user with rollback immediate"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="DBCC CHECKDB ($vCenterDB,REPAIR_ALLOW_DATA_LOSS)"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="DELETE FROM VPX_TEXT_ARRAY WHERE NOT EXISTS(SELECT 1 FROM VPX_ENTITY WHERE ID=VPX_TEXT_ARRAY.MO_ID)"
try 
{
    WRITE-HOST "Please be patient, the next query can take a long time to complete (15+ minutes)"
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop' -verbose
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}

$sqlquery="alter database $vCenterDB set multi_user"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}
WRITE-HOST ""



# Waiting x seconds for DB to recover from truncate
$delay=5
WRITE-HOST "-------------------- $delay Second delay --------------------"
Start-sleep -s $delay
WRITE-HOST "Waited for $delay second(s), continue running script"
WRITE-HOST ""


WRITE-HOST "-------------------- Starting Services --------------------"
# Start vCenter Server
$services = @("VMware VirtualCenter Management Webservices", "Vmware VirtualCenter Server")
foreach ($service in $services)
{
    try
    {
WRITE-HOST "Starting Service: $service"
WRITE-HOST "Please wait, this can take a few minutes"
        Start-Service -displayname $service -ErrorAction Stop
WRITE-HOST "$service Succesfully started" -Foregroundcolor "green"
WRITE-HOST ""
    }
    catch
    {
WRITE-HOST "Error starting service: $service"
WRITE-HOST "$_.Exception.Message" -foregroundcolor "red"
WRITE-HOST "Exit script due to previous error" -foregroundcolor "red"
exit
    }
}


# Waiting x seconds for vCenter to start operation
$delay=60
WRITE-HOST "-------------------- $delay Second delay --------------------"
Start-sleep -s $delay
WRITE-HOST "Waited for $delay second(s), continue running script"
WRITE-HOST ""


#Connect to vCenter Server
WRITE-HOST "---------------- Connect to vCenter Server-------------------"
try
    {
WRITE-HOST "Connecting to: $VCServer"
        $VC = Connect-VIServer $VCServer -User $username -Password $password
WRITE-HOST "Succesfully connected" -Foregroundcolor "green"
WRITE-HOST ""
    }
    catch
    {
WRITE-HOST "Error connecting to $VCServer"
WRITE-HOST "$_.Exception.Message" -foregroundcolor "red"
WRITE-HOST "Exit script due to previous error" -foregroundcolor "red"
exit
    }

# Waiting x seconds for vCenter to start operation
$delay=5
WRITE-HOST "-------------------- $delay Second delay --------------------"
Start-sleep -s $delay
WRITE-HOST "Waited for $delay second(s), continue running script"
WRITE-HOST ""

#Reconnect Hosts
WRITE-HOST "---------------- Reconnect Hosts to resync -------------------"
try
    {
$vmhosts = get-vmhost
foreach ($vmhost in $vmhosts)
    {
set-vmhost -VMHost $vmhost -State disconnected
WRITE-HOST "Succesfully disconnected $vmhost" -Foregroundcolor "green"
set-vmhost -VMHost $vmhost -State connected
WRITE-HOST "Succesfully connected $vmhost" -Foregroundcolor "green"
    }
    }
    catch
    {
WRITE-HOST "Error reconnecting hosts to $VCServer"
WRITE-HOST "$_.Exception.Message" -foregroundcolor "red"
WRITE-HOST "Exit script due to previous error" -foregroundcolor "red"
exit
    }

#Reconnect Hosts
WRITE-HOST "---------------- Disconnect from vCenter -------------------"
try
    {
WRITE-HOST "Disconnecting from vCenter server: $VCServer"
Disconnect-VIServer -Confirm:$false
WRITE-HOST "Succesfully disconnected" -Foregroundcolor "green"
WRITE-HOST ""
    }
    catch
    {
WRITE-HOST "Error disconnecting from $VCServer" -foregroundcolor "red"
WRITE-HOST "$_.Exception.Message" -foregroundcolor "red"
WRITE-HOST "Exit script due to previous error" -foregroundcolor "red"
exit
    }


#Reconnect Hosts
WRITE-HOST "---------------- Shrink Database -------------------"

$sqlquery="DBCC SHRINKDATABASE ($vCenterDB)"
try 
{
    Invoke-SQLcmd -ServerInstance $SQLInstance -Database $vCenterDB -Query $sqlquery -QueryTimeout 65535 -ErrorAction 'Stop'
    WRITE-HOST "[OK] '$sqlquery'" -foregroundcolor "green"
} catch {
    WRITE-HOST "Error when running '$sqlquery'" -foregroundcolor "red"
    WRITE-HOST($error) -foregroundcolor "red"
}
WRITE-HOST ""

WRITE-HOST ""
WRITE-HOST ""
WRITE-HOST "Done executing script, if you notice any problems you can restore the SQL database backup from this location: '$backupfile'"
WRITE-HOST ""

[Unison]

Hi All,
been a couple years now - with VMWare 6 and Veeam9 running around.......is this problem resolved yet?
We have had replication disabled for a couple years now because of this - and i would really like to start using replication again.......but not if this problem is still happening.

[rntguy]

we're finding that our VPX_TEXT_ARRAY table is the culprit due to a plethora of snapshot entries, presumably from the hourly replication jobs. is this a bug or by design? I am following this article right now to clean it out but makes me nervous. https://kb.vmware.com/s/article/2005333

[Vitaliy S.]

I guess that might vCenter Server needs to log all the events that are happening to the VM (such as snapshot creation/deletion), so it might be a design issue. Following that KB under VMware support team supervision should be a way to go (if you're nervous )