Event DB grows huge, really fast after upgrade to 7.4

[srodenburg]

Hello,

Environment:

Lab with 2 x View Connection Broker v7.4 on Win2016. An external SQL Database server running SQL2014 Enterprise.

Problem:

After upgrading from 7.3 to 7.4, the Event DB, running on the external SQL Server, grows super fast to enormous proportions. It will fill the disk the DB is on, or if the DB has a file-size-limit, grow to that size. After that, it turns "red" inside the Horizon Admin GUI (because it cannot write new records, obviously).

As this is just a lab, I un-configured the event DB and deleted the humongous DB (it was about 80 GB in size...) from the SQL Server.

While the event DB is not configured (shows up as corresponding yellow in the Admin GUI), both Connection Servers went from "almost no CPU usage" to "80% or more CPU Usage all the time".

Then:

On the SQL server I created a new fresh database and limited the file-size to 1 GB.

I configured a Event DB again, the CPU's of both connection servers came to rest (almost no usage, this is a lab and just sits idle)

Within 30 minutes, the DB file grew to 1 GB.

This problem is 100% repeatable.

In the logs on the connection servers (C:\ProgramData\VMware\VDM\logs), I see nothing that grabs my attention relating to this issue.

All i see in the regular log file are these errors:

"[EventDBConnection] Could not reconnect to Events database: Unable to create events tables: Could not allocate space for object 'sys.sysobjvalues'.'clst' in database 'HorizonViewEventDB' 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"

Which is completely logical as the 1 GB DB-file is full. If I change the size to 2 GB, within 30 minutes, that will be full too. etc. etc. etc.

In the Debug logfile, I don't see anything dodgy either.

My question is: i'm not a SQL person. How do I go about troubleshooting this? I need to find out "with what is the DB getting filled at such an insane rate". Is there a SQL query that I can run to see the last X hundred messages that came in before the DB reached it's size limit?

[BenFB]

My View Events database on 7.4.0 is only 10 GB and services thousands of users a day. The database is also about a year old and has been through a few Horizon upgrades.

Try launching the SQL Server Management Studio, locate and expand the database, Tables, right-click VE_event and Select top 1000 rows. My guess is you have an error that is repeating over and over.

If you aren't comfortable with SQL and you have a support contract create a SR with VMware and they should be able to sort it out.

[srodenburg]

Hello Ben,

This is a Lab where at the most, 10 to 20 logins happen a day.

"My guess is you have an error that is repeating over and over."

That is what I hoped too. But nope...

I've been digging. As it turns out, the EventDB is being dumped full with really old records. When I clear the DB in View Admin GUI (so not using a DB at all), then delete the DB on the SQL Server, re-create it and tell View to use it, it immediately blows up again. When I query the various tables, I see records coming in from many months ago, until today.

**Something** is pumping really old stuff into the EventDB at a phenomenal rate. That is super obvious. Question is: WHY ?

If I repeat the process of "not using a DB", delete the DB, re-create, start using an Event DB again, it starts all over again.

So where does this old information come from?  I thought about View dumping logfiles into the EventDB, but the logs rotate normally and the oldest ones are only a couple of days old. It absolutely makes no sense to me.

This is a Lab so no support.

If I run this simple query:

use HorizonViewEventDB

select count(*) from [dbo].[VE_event]

select count(*) from [dbo].[VE_event_data]

select count(*) from [dbo].[VE_event_data_historical]

select count(*) from [dbo].[VE_event_historical]

I get these numbers (of records) back:

543200

2065696

2065768

543800

This is after roughly 30 minutes of creating a brand new database. And each time, after each "database delete and re-create cycle" the same records return.

When I do this a couple of minutes later, the numbers have increased big time.

This is a sample when I simply let it show the first 1000 records.

EventID	Module	EventType	ModuleAndEventText	Time	Source	Severity	Node	Acknowledged
1	Broker	BROKER_USERLOGGEDOUT	User LAB\admsro has logged out	2018-03-05 10:51:54.993	net.propero.modules.properOps.UserSessionTracker	AUDIT_SUCCESS	ViewConn01.lab.local	1

That is a logout message from March 2018. Where the h*ll does this come from.

[BenFB]

Have you opened a SR with VMware? They might be better suited to look at the database and figure out what is going on.

[IBMChakresh]

Hello Srodenburg,

Did you find a solution for this ? I am also facing the same issue in my newly created lab environment in 7.3.

[jasonboche]

I fought this for a few days. VMware Horizon 7.6.0 build-9823717. My MS SQL database was bloated and after trying a few different procedures to essentially empty the database, Horizon kept shoving the same millions of old rows into it from several months ago. Following are some steps I went through and the results. I do'n't think the order matters except for the last step - which appears to have solved the problem.

1. Clear the Horizon Event configuration. Delete the 80GB database. Recreate the 80GB database. Reconfigure the Horizon Event configuration. Result: Horizon kept shoving the same millions of old rows into it from several months ago.
2. Clear the Horizon Event configuration. Delete the 80GB database. Recreate the 80GB database. Reconfigure the Horizon Event configuration. Result: Horizon kept shoving the same millions of old rows into it from several months ago. I know, getting close to the definition of insanity but what's happening can't be right.
3. After checking first for foreign keys on each table and finding none, created and ran a MS SQL query to empty the contents of the 5 tables. Result: Horizon kept shoving the same millions of old rows into it from several months ago.

use viewevents

go

truncate table [dbo].[veevent]

go

truncate table [dbo].[veevent_data]

go

truncate table [dbo].[veevent_data_historical]

go

truncate table [dbo].[veevent_historical]

go

truncate table [dbo].[veprops]

go

     4. Decided I don't care about Horizon Events so I just whacked the database and I won't use one going forward. Result: this turned out to be a really bad idea as the CPU utilization of the VMware Horizon Connection Server went up to 100% and stayed there. VMware Horizon View Java Component Service 64-bit (VMware Horizon View Web Component) was apparently stressing out over the millions of events it wanted to log with no place to log them. So I recreated the database and Horizon Event configuration.

     5. Deleted many text based logs from c:\ProgramData\VMware\VDM\ and the various subdirectories. Stopped the VMware View Horizon service(s). Cleared out the MS SQL using steps above. Restarted the VMware View Horizon service(s). Result: Horizon kept shoving the same millions of old rows into it from several months ago.

     6.  ***This is the step that seems to have worked (thus far)***. In VMware Horizon 7 Administrator, Event Configuration, Edit Event Settings. Show events in View Administrator for: change from default of 3 months to 1 week. Cleared out the MS SQL using steps above. Restarted the VMware Horizon Connection Server. Cleared out the MS SQL using steps above. Went to bed and let the Horizon Connection Server sit overnight (12 hours). Result: the following morning I found all 5 tables completely empty. The Horizon Connection Server has not inserted any rows into the database since reboot. For good measure, I decided to Clear the Horizon Event configuration. Delete the 80GB database. Recreate the 80GB database. Reconfigure the Horizon Event configuration. Result: Horizon inserted a few new rows into the fresh database regarding the new Event configuration. Logged off and logged on again. Horizon is now logging relevant Event information and seems to have completely let go of the millions of old events. One other thing to note is that resetting the Event Configuration also resets the retaining of events back to the default of 3 months.

As I have no VMware support for this environment, Step 7 was going to be rebuild the Windows 2012 R2 View connection Server to Windows Server 2016 or maybe 2019 but that's a pain in the rear end setting up the accounts, pools, etc. so I was reserving it to the very end.

Good luck to anyone else who runs into this.