1 2 3 Previous Next 31 Replies Latest reply on May 12, 2014 3:31 AM by MrBeatnik

    vCenter Database full after a few days

    the_xploit Novice

      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>

        • 1. Re: vCenter Database full after a few days
          schepp Virtuoso
          User ModeratorsvExpert

          Hi,

           

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

           

          Have you changed the default logging levels of vCenter?

          • 2. Re: vCenter Database full after a few days
            MGlasson Novice

            +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.
            • 3. Re: vCenter Database full after a few days
              the_xploit Novice

              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!

              • 4. Re: vCenter Database full after a few days
                Borja_Mari 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

                • 5. Re: vCenter Database full after a few days
                  the_xploit Novice

                  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

                  • 6. Re: vCenter Database full after a few days
                    Borja_Mari Virtuoso

                    Sorry!,

                    hehehe

                     

                    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

                     

                     

                    Best regards,

                    Pablo

                    • 7. Re: vCenter Database full after a few days
                      the_xploit Novice

                      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!

                      • 8. Re: vCenter Database full after a few days
                        Borja_Mari 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&sliceId=1&docTypeID=DT_KB_1_1&dialogID=181358352&stateId=0%200%20181364209

                        http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&docType=kc&externalId=1028356&sliceId=1&docTypeID=DT…

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

                         

                        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)

                        1 person found this helpful
                        • 9. Re: vCenter Database full after a few days
                          King_Robert 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.

                          • 10. Re: vCenter Database full after a few days
                            MGlasson Novice

                             

                            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

                             

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

                             

                            excuse my delay getting back to you.

                            1 person found this helpful
                            • 11. Re: vCenter Database full after a few days
                              memaad Master

                              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

                              • 12. Re: vCenter Database full after a few days
                                the_xploit Novice

                                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

                                 

                                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?

                                • 13. Re: vCenter Database full after a few days
                                  wmarusiak Enthusiast
                                  vExpert

                                  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

                                  • 14. Re: vCenter Database full after a few days
                                    raog Expert

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

                                     

                                    Regards

                                    Girish

                                    1 2 3 Previous Next