VMware Cloud Community
the_xploit
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
schepp
Leadership
Leadership

Hi,

how many ESXi hosts and VMs do you manage with your vCenter?

Have you changed the default logging levels of vCenter?

Reply
0 Kudos
MGlasson
Enthusiast
Enthusiast

+1 for Tims answer,

the transaction log problem occurs when the recovery mode is set to full, are you sure you have set the recovery mode to simple.

also don't keep the performance data for too long as this will fill the database.

To view or change the recovery model of a database with SQL Server Management Studio.

  1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
  3. Right-click the database, and then click Properties, which opens the Database Properties dialog box.
  4. In the Select a Page pane, click Options.
  5. The current recovery model is displayed in the Recovery model list box.
  6. Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.
Reply
0 Kudos
the_xploit
Contributor
Contributor

Hi

Thanks you guys for try to help me!!

MGlasson wrote:

the transaction log problem occurs when the recovery mode is set to full, are you sure you have set the recovery mode to simple.

also don't keep the performance data for too long as this will fill the database.

this is mentioned above as steps i already did... But i double checked it for you and even made a screenshot...

20-02-2014 11-31-37.png

and here the other settings:

20-02-2014 11-28-45.png20-02-2014 11-29-01.png

Settings should be fine like this right?

wrote:

how many ESXi hosts and VMs do you manage with your vCenter?

Have you changed the default logging levels of vCenter?

- I manage 2 ESXi 5.0 hosts with about 12 vm

- i dont think so.. see above "Logging options = normal logging". That should be fine right?

Best Regards!

Reply
0 Kudos
Borja_Mari
Virtuoso
Virtuoso

Hello,

you can check this documentation about changing the recovery model of a sql server database.

With the simple recovery model, if you want to backup this database, you should do full backups, because now the sql server transaction log is basically useless.

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
Reply
0 Kudos
the_xploit
Contributor
Contributor

no offence

but did you even read my post? => my first post says i have simple mode, my second post says i really have simple mode and moreover i made a screenshot of my DB options... Or did i miss something?

Nevertheless thx for your time

Reply
0 Kudos
Borja_Mari
Virtuoso
Virtuoso

Sorry!,

hehehe Smiley Wink

Ok, now i really understand your issue.

You have set the recovery model to "simple", but your vcenter transaction log is getting full again, right?

mmm ...

I would recommend you to really wipe the transaction log:

  1. Stop vCenter services, of course!
  2. Do a db backup just for security
  3. Detach the db
  4. Wipe the transaction log
  5. Attach the db again
  6. Start Vcenter services again

This should fix your 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
Reply
0 Kudos
the_xploit
Contributor
Contributor

hehe exactly!

But the problem is not to get rid of my too big T-Log (i always just shrink files and DB and T-Log gets back to normal)

the problem is that i have to do that all few days!! (if vCenter is not running i cannot manage my vm's and my backup (BE 2012) will also not run because vCenter is not reachable) so i really need a sable vCenter Server which does not need me to clear T-Log all few days....

I just found a hint by myself. The T-Log today filled up in just 3 hours!! And i now maybe know why: one Backup ran exactly in this timeframe... so i think T-Log is so big because of "a very large DB operation" -> make a backup (i think Be2012 clones machines in the background or at least makes some snapshots and these actions are filling up my T-Log which can not be cleared until operation is finished... This reason would also fit in my first obeservation when vCenter crsahed when i wanted to make some clones manually.

If this is the case it means i need a MS-SQL License (2.5k $) to have a stable vCenter Server! WTF! Why the f** vmware does not let us use mySQL?? neighter have this issue nor need time to care about!

I mean i have an SQL Server with license but that is another server and there are our core-business application DB's which is not a good place for this. Moreover if the DB is on another Server there are two more Problems that can make vCenter stop (network, and the other server => vCenter is not idenpendant then => this also leads to problems when using vCenter to shutdown your ESXi Server when UPS switches on beacuse as soon as i shutdown my SQL Server vCenter will go down aswell...)

How you guys handle that problem? You all have SQL Server with license to have bigger DB's as 10GB? and why would vmWare suggest using SQL Express when even a simple clone fills up T-Log with 10GB of DATA??

By the way: 10GB Logs for a clone makes 0 sense... What is wrong with my setup!?

Can somebody clone a running machine and check how much bigger the log gets?

And/Or can somebody that uses SQL Express tell me if he ever had such a problem?

and anyone has some experience witch Backup Tools like Backup exec 2012 or similar and it's side effects on the T-Log?

Any help is appreciated!

Reply
0 Kudos
Borja_Mari
Virtuoso
Virtuoso

Hi,

IMHO maybe you should license your sql server, because the express edition has a database limit of 4-10 GB per database ... when the limit is reached, the database denies more data ...

If you try to keep using the express edition, then you should try to periodically shrink the vcenter database:

http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&docType=kc&externalId=1025914&sl...

http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&docType=kc&externalId=1028356&sl...

VMware KB: Reducing the size of the vCenter Server database when the rollup scripts take a long time...

and the transaction log:

http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs

DBCC SHRINKFILE (Transact-SQL)

using manually created jobs.

Best regards,

Pablo

Message was edited by: Pablo (added how to shrink the transaction log)

------------------------------------------------------------------------------------------------- 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
King_Robert
Hot Shot
Hot Shot

The SQl Server 2008 R2 Express Edition is installed as default SQL server with VCenter 5.1 and database size limit is 10 GB in SQL 2008 R2.

you have to either install the SQL server 2008 R2 standard Edition so that there is no limitation of database.

The second option is to set the database retention limit in VCenter server data store option.

Reply
0 Kudos
MGlasson
Enthusiast
Enthusiast

and here the other settings:

20-02-2014 11-28-45.png20-02-2014 11-29-01.png

Settings should be fine like this right?

Those settings are fine can you also look at the statistics settings.

Statistics can chew up a whole lot of your database.

for instance my vcenter is set to the below

pastedImage_3.png

also do you have sql server agent running on the server, and do you have the pictured below jobs set to run

pastedImage_4.png

excuse my delay getting back to you.

memaad
Virtuoso
Virtuoso

Hi,

Do you have any monitoring tool which keep polling ESXi host and vCenter server, just to make sure that task and event is not filling up your  database.

Regards

MOhammed Emaad

Mohammed | Mark it as helpful or correct if my suggestion is useful.
Reply
0 Kudos
the_xploit
Contributor
Contributor

Thx alot for your help! To sum this thread up:

My Settings / Setup:

- Settings should be fine (ty for your stats MGlasson)

- i now also checked statistics but with my 2 physical hosts and my not even 20 machines it gives me 0.47GB DB size... => no problem because i can grow to 10GB (ty for your hint with statistics MGlasson)

Solutions so far:

- manually created jobs that shrinks DB / Files (ty Pablo)

What could still help further?

1) It is still unclear why the T-Log grows so fast!? (Today i cloned another machnie with absolutly zero effect on T-Log) some information to this point:

     - the DB (VIM_VCDB) seems not to be the problem after changing the retention policy and other settings... It seems that only the T-Log gets full from time to time...

     - the T-Log is now 9.9GB but my vCenter is still running! When i shrink DB now it will take like 5Min and T-Log is empty

2) are there any scripts that do that schrinking? (And why they are not built-in if this is the only workaround!?)

3) are there any other solutions on preventing T-Log from getting filled (rsp. help getting smaler again..other than shrinking)?

MGlasson wrote:

also do you have sql server agent running on the server, and do you have the pictured below jobs set to run

36036_36036.pngpastedImage_4.png

excuse my delay getting back to you.

4) What is it about that SQL Server agent? Never heard of that! Where should i find that agent? And where is your screenshot coming from? SQL Mgmt Studio?

Reply
0 Kudos
wmarusiak
Enthusiast
Enthusiast

I had same issue some time ago in development environment.

What I did I basically restricted grow of tempdb to 4GB and templog to 1GB. I have environment which has ~20 hosts and ~300VM's and I didn't notice performance issues. I hope it helps.

temdb.png

Best Regards, Wojciech https://wojcieh.net
raog
Expert
Expert

Did you guys consider an alternate solution of using the vcenter appliance?

Regards

Girish

To Virtualization and beyond! PS::If you felt the answer as helpful, please mark it as helpful/answered so that it helps other users as well! Blog:: www.virtualtipsntricks.com
Reply
0 Kudos
Borja_Mari
Virtuoso
Virtuoso

Hello,

i will try my best to help you Smiley Wink

1) IMHO, the transaction log of your vcenter db has not a "normal" behavior. Using the simple recovery model, the transaction log should be so small!

I have checked sql server expression databases (in simple recovery model) of some vcenters that I'm administering (some bigger than yours), and its transaction logs are smaller than 1 GB, and not really grows over time.

if you are really in the recover model, if the transaction log size increases so quickly, maybe is produced by a long running transaction.

The sql server do log truncation automatically. If the log truncation is delayed (maybe long running transaction?), the reason can be discovered.

2) You can also using manually created jobs (as i commented before in this discussion), to try to shrink periodically the transaction log.

3) IMHO, the only reason to have the transaction log increasing so fast in recovery mode, should be long running transactions. Anyway, I'm not sure that in your small environment, it's a normal behavior seeing this long running transactions ...

4) The sql server agent isn't included with the sql server express edition, then things like db jobs, just can be done manually as i commented before.

Basically, the only way/tool to administer/manage a sql server express version, is using the sql management studio express tool.

Best regards,

Pablo

P.D:

Here you can check some (good?) explanation about the recovery modes in sql server.

------------------------------------------------------------------------------------------------- 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
Reply
0 Kudos
the_xploit
Contributor
Contributor

Thank all of you for your help!

@wmarusiak: Very good idea!! I just changed my settings (restricted growth to 9GB).. That should solve my problem if the files really don't get bigger than this! I will now check the next few days if vCenter is sable now.. (i will notice it very fast because today vCenter was down again after 4 days of idle running...)

@raog: we didn't. What would be alternatives? (..btw. if a vmWare - tool has some issues i don't wanna know about the issues i have with 3rd party tools...)

@borja_mari: thx for your help. And yes good link (already read it) if restricted growth not working i will face up to scripts to shrink and or backup DB...

Reply
0 Kudos
Borja_Mari
Virtuoso
Virtuoso

Hello,

nice to know that we helped you Smiley Wink

Give us feedback if you finally are be able to fix the 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
Reply
0 Kudos
the_xploit
Contributor
Contributor

Like i said before i restricted the growth of T-Log to max size of 2GB. T-Log is already filled up (in about 2h) and vCenter is still running!

DB looks like this a.t.m.:

26-02-2014 15-50-38.png

So it seems like SQL Server and vCenter can handle this restriction without any issues... Whats next?

- will vCenter still run in 3 days?

- if T-Log problem is solved may be i run into problems with the DB itself.. last week the DB was about 4.5GB yesterday it was 5GB and today it is 5.1GB... always when i shrieked my T-Log the DB grew about 100MB -> no problem but i'm in fear that these Data cannot be auto-deleted by vCenter... WE WILL SEE...

I will come back to give some feedback and maybe some more problems...

Reply
0 Kudos
raog
Expert
Expert

The VC Appliance doesnt have the 5 hosts 50 VMs restriction like the windows VC and the database size increase shouldnt be this problematic. The only downside as of now is not having VUM/SRM etc on the appliance. If you have a relatively small setup to administer, you might want to test this out.

Regards

Girish

To Virtualization and beyond! PS::If you felt the answer as helpful, please mark it as helpful/answered so that it helps other users as well! Blog:: www.virtualtipsntricks.com
Reply
0 Kudos