Highlighted
Contributor
Contributor

vCenter Database full after a few days

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>

31 Replies
Highlighted
Contributor
Contributor

***News***

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

but i might found a problem:
05-03-2014 15-17-58.png

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??

0 Kudos
Highlighted
Virtuoso
Virtuoso

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 Smiley Happy

Best regards,

Pablo


------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
0 Kudos
Highlighted
Contributor
Contributor

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...

0 Kudos
Highlighted
Virtuoso
Virtuoso

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

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
0 Kudos
Highlighted
Contributor
Contributor

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

namedatabase_idrecovery_modelrecovery_model_desclog_reuse_wait_descsource_database_idcreate_datecompatibility_levelcollation_nameuser_accessuser_access_descis_read_onlyis_auto_close_onis_auto_shrink_onstatestate_descis_in_standby
master13SIMPLENOTHINGNULL08.04.2003 09:13100Latin1_General_CI_AS0MULTI_USER0000ONLINE0
tempdb23SIMPLENOTHINGNULL19.03.2014 14:36100Latin1_General_CI_AS0MULTI_USER0000ONLINE0
model33SIMPLENOTHINGNULL08.04.2003 09:13100Latin1_General_CI_AS0MULTI_USER0000ONLINE0
msdb43SIMPLENOTHINGNULL02.04.2010 17:35100Latin1_General_CI_AS0MULTI_USER0000ONLINE0
RSA53SIMPLENOTHINGNULL30.11.2012 15:59100Latin1_General_CI_AS0MULTI_USER0110ONLINE0
VIM_VCDB63SIMPLENOTHINGNULL30.11.2012 16:24100Latin1_General_CI_AS0MULTI_USER0100ONLINE0
VIM_UMDB7

3

SIMPLENOTHINGNULL28.01.2013 16:16100Latin1_General_CI_AS0MULTI_USER0000ONLINE

0

0 Kudos
Highlighted
Virtuoso
Virtuoso

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 Smiley Sad

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

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
0 Kudos
Highlighted
Immortal
Immortal

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.

0 Kudos
Highlighted
Virtuoso
Virtuoso

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 Smiley Happy

Best regards,

Pablo

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
0 Kudos
Highlighted
Virtuoso
Virtuoso

Hello,

did you finally resolve your transaction log issue? Smiley Happy

Best regards,

Pablo

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
0 Kudos
Highlighted
Contributor
Contributor

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!

0 Kudos
Highlighted
Virtuoso
Virtuoso

Nice to know you finally have fixed your issue Smiley Wink

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
0 Kudos
Highlighted
Hot Shot
Hot Shot

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

root.png

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

0 Kudos