vCenter Database full after a few days

[the_xploit]

Hey

I'm really new to VMware and just set up vCenter Server. Everything ran fine until vCenter was not reachable anymore.

The reason: my Transaction Log is full (it was 9.9GB of 10GB max space available for free MS SQL Express version). The solution from VMware is:

- shrink DB

- shrink Files

- set Recovery Mode to Simple

- set retention policy to min. to have DB as small as possible

i did all of it and had a few problem-free days. But then i created some clones => DB full (_not_transaction log), vcenter dead

i did all of it again.... and retried to create a clone => transaction Log full, vcenter dead

i did it again... all good when not cloning.... i gave up on cloning and found another workaround. Today i came to the office and vCenter is dead another time... Why? Transaction Log full (without doing anything!!).

what the hack am i doing wrong? The aim is to have a vCenter that runs without me cleaning the DB every few days...

Since i shrinked the DB only TransactionLog creates problems. so DB itself (with retention policy!?) seems to work but TL is still full from time to time.. So Question for me is how i can prevent the TransactionLog from getting full?

Any help is appreciated!

<hateing>

I dont have vCenter for any Logs or crap i just want to manage my ESXi servers from there!! Imagine your ESXi would crash if his log is full!! What logic is that!

So where can i choose between "a longterm running vCente without logs" or "a vCenter that will be down every few days (but you have the logs!!)"? ^^

// what could be better in my point of view //

mySQL over MS SQL => why there is no mySQL driver?? its free that's why!? capitalism!?

limit in MB over retention policy => a retention policy can safe you from growing DB but i does not have to. A limit in MB would do it always

</hateing>

[the_xploit]

***News***

vCenter is down! T-Log as full as always.

but i might found a problem:

It looks like I'm logging-in and -out all 1-5 Seconds. I found this by a hint i found in this article: VIM_VCDB grows too fast

I have a total of 5million rows in VPX_EVENT a another 5 millions VPX_EVENT_ARG...

i deleted the first million rows from VPX_Event and my T-Log grew to 8GB!! Now the transaction is finished and the T-Log is still 8GB.

****QUESTIONS****

- is 5m Rows normal in VPX_Events?

- is login logoff - event every few second normal? And why is this happening?

- how can deletion of 1m rows lead to an increase in T-Log of 6GB (it was 2GB big before i started the deletion...) and WHY IT STAYS LIKE THIS??

[Borja_Mari]

Hello,

obviously, it isn't a normal behavior the way your VPX_EVENT and VPX_EVENT_ARG tables grows in time ...

These tables store the event information from the Tasks and Events tab in vCenter Server.

You should investigate the source of such log-in and log-out and maybe disable/deactivate it, like the creator of the discussion you mentioned did.

Then, talking about the transaction log, the transaction log records all transactions that occur on the database.

Using simple recover, the database logs all transactions, but after the transaction is complete, it is deleted. It uses the least amount of disk space of all the models.

Maybe you have already checked this vmware's KB:
VMware KB: Purging old data from the database used by VMware vCenter Server 4.x and 5.x

Until you disable this huge amount of log-in and log-out, maybe a (bad) workaround will be setting vcenter logging to warning in "vcenter server settings -> logging options" and events retained for just 1 day in "vcenter server settings -> database retention policy".

Hope this helps

Best regards,

Pablo

[the_xploit]

Pablo: "You should investigate the source of such log-in and log-out and maybe disable/deactivate it, like the creator of the discussion you mentioned did."

Me: He solved the problem by shutting down his vMA (vmware virtual management appliance) which made all these Log-In and Log-Out entries. I don't have a vMA (even tho i had one a long time ago..). _But how you pointed out correctly i have to find the part of software that causes these logIn LogOut attempts.

Pablo: "Then, talking about the transaction log, the transaction log records all transactions that occur on the database.

Using simple recover, the database logs all transactions, but after the transaction is complete, it is deleted. It uses the least amount of disk space of all the models."

Me: ...i have simple mode... Nevertheless the transaction log was 8GB even _AFTER_ successfully complete the transaction. In my understanding (and also your understanding) the T-Log should shink again after complete the transaction. (but it doesn't!!)

What's next?

- i try to find the cause of all these LogOn-entries. I'm interested in all information regarding the origination of these Log-entries. My best guess is Backup Exec 2012 (which definitely makes some of these events). I will come back with feedback...

[Borja_Mari]

Hello again,

IMHO until you disable the massive Log-In and Log-out, your ms sql server will be working in this way ...

Maybe you should increase the size of your T-Log.

The sql server in simple recover mode, periodically truncates the t-log (this doesn't shrink it or reduces the t-log file size).

Anyway, maybe this log truncation is delayed for some reason.

Usually when the t-log becomes full, in the windows event log appears a message telling to check the log_reuse_wait_desc column of the sys.databases view, in order to know the reason because the t-log space can't be reused (a t-log truncation can't be done)

Best regards,

Pablo

[the_xploit]

Pablo wrote:

Usually when the t-log becomes full, in the windows event log appears a message telling to check the log_reuse_wait_desc column of the sys.databases view, in order to know the reason because the t-log space can't be reused (a t-log truncation can't be done)

That's right! I have a lot of these Messages. If i look for these Information it shows (see below table). log_reuse_wait = 0 and log_reuse_desc = NOTHING

The only two things left for me is:

- re-install vCenter and hope that solves the problem = > hope... lol!!! :smileycry:

- install a non-express Version of SQL Server to see if a T-Log with let's say 20GB is enough... => i think the only thing this changes is that the T-Log is now 20GB but still gets full and still crashes my vCenter

name	database_id	recovery_model	recovery_model_desc	log_reuse_wait_desc	source_database_id	create_date	compatibility_level	collation_name	user_access	user_access_desc	is_read_only	is_auto_close_on	is_auto_shrink_on	state	state_desc	is_in_standby
master	1	3	SIMPLE	NOTHING	NULL	08.04.2003 09:13	100	Latin1_General_CI_AS	0	MULTI_USER	0	0	0	0	ONLINE	0
tempdb	2	3	SIMPLE	NOTHING	NULL	19.03.2014 14:36	100	Latin1_General_CI_AS	0	MULTI_USER	0	0	0	0	ONLINE	0
model	3	3	SIMPLE	NOTHING	NULL	08.04.2003 09:13	100	Latin1_General_CI_AS	0	MULTI_USER	0	0	0	0	ONLINE	0
msdb	4	3	SIMPLE	NOTHING	NULL	02.04.2010 17:35	100	Latin1_General_CI_AS	0	MULTI_USER	0	0	0	0	ONLINE	0
RSA	5	3	SIMPLE	NOTHING	NULL	30.11.2012 15:59	100	Latin1_General_CI_AS	0	MULTI_USER	0	1	1	0	ONLINE	0
VIM_VCDB	6	3	SIMPLE	NOTHING	NULL	30.11.2012 16:24	100	Latin1_General_CI_AS	0	MULTI_USER	0	1	0	0	ONLINE	0
VIM_UMDB	7	3	SIMPLE	NOTHING	NULL	28.01.2013 16:16	100	Latin1_General_CI_AS	0	MULTI_USER	0	0	0	0	ONLINE	0

[Borja_Mari]

Hello,

recently i faced similar issue than you, and this: Re: Solution to full transaction log for database VIM_VCDB helps me.

I would like to explain exactly my issue and how i fixed it, but i don't have time to write it in my blog

Maybe some db routine (stored procedure?) needs so much space in the t-log, and it overflows the t-log and this routine really don't finish. And every time the same problem happens.

Best regards,

Pablo

[admin]

To shed some light on the transaction log size with the SQL express and simple recovery model.

You do not have an SQL Server agent with the Express version, therefore everything on the database uses stored procedures and no jobs to move data from one table to another. This basically gets logged into the transaction log, when one full transaction is finished this should be automatically cleaned out of the log afterwards.

The SQL Express is not only limited in size but also in CPU and memory resources. What you are currently doing is hammering your artificially undersized (because MS actually wants to see money for full performance) SQL server with I/O and CPU usage with all the transactions for the logins and logouts.

Something is using your domain user to establish a connection to vCenter server. If you have a look at the tasks and events view for the events on vCenter level in the vSphere client you will see username@ip logged in messages flowing. The IP is important, if it is an external IP, look at that system to determine what is installed on there that could connect to vCenter Server. If it is local it is a service on the local vCenter Server system itself.

I have seen several vCenter Server plugins causing that kind of issue (NetApp from the top of my head in an older version), monitoring solutions like Nagios that are very aggressive also can cause those issues. I have also seen Heartbeat causing this kind of issue. It is pretty hard to determine what exactly it is and usually involves some kind of guessing to find the correct service to tweak.

You could buy a full SQL server license (I think standard starts at around 500 Euro or something like that, didn't check in quite a while) which would "resolve" your issue in the way that you are not crashing anymore because of a space limitation. Until your disk would be completely full and you would be crashing because of that. So full SQL would only mask the symptoms for a little longer but would not be a long term solution.

[Borja_Mari]

Hello,

finally i write an entry in my blog to document the full transaction log issue i faced, and how i fixed it.

Hope it helps you

Best regards,

Pablo

[Borja_Mari]

Hello,

did you finally resolve your transaction log issue?

Best regards,

Pablo

[the_xploit]

I could not find a suitable solution for me. The only solution i had was creating a SQL-Job that cleans the database on regular bases. But this is not a real solution it is more like minimize the effect instead of really solve the cause of the problem.

I now decided to reinstall vCenter. After i read, that the vCenter Appliance has its own embedded databases for SSO and vCenter, i decided to use the appliance because i hope that the embedded DB will not have this issues... We will see...

I deployed the OVA Template and after some problems on adding to the domain all runs fine now and vCenter is stable for more than a week now. I'm happy with that! I let you guys know when i run into the same problem with the appliance.

As far as i can judge it - use vCenter Appliance!

Thanks alot for your help!

[Borja_Mari]

Nice to know you finally have fixed your issue

[MrBeatnik]

Just thought I would throw in my experience, and how I resolved (VC in relation to View Horizon). It's not exactly the same, but may help someone along the way.

Our database was growing too large.

On examining the EVENT tables, we also found login/logouts (slightly different).

Cleanup was a pain, as all space was taken up.

Cleaning out 7 million events at a time would create a massive transaction log, which there wasn't space for.

I ended up doing a query for 1 month of events, and deleting those. Performed DB truncate then Rinse and repeat.

We are running HP Gen 8 servers, so specifically for us, we had this solution (only reported in Jan by VMware):

VMware KB: VMware vCenter Server database grows with the logging of root@127.0.0.1 events

But of course, we could also just limit the number of events being logged from the VC console:

Administration > vCenter Server Settings > Database Retention Policy > Events retained for x days