1 2 3 Previous Next 31 Replies Latest reply on May 12, 2014 3:31 AM by MrBeatnik Go to original post
      • 15. Re: vCenter Database full after a few days
        Borja_Mari Virtuoso

        Hello,

        i will try my best to help you

         

        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.

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

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

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

            Hello,

            nice to know that we helped you

             

            Give us feedback if you finally are be able to fix the transaction log issue

             

             

            Best Regards,

            Pablo

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

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

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

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

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

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

                     

                     

                    Best regards,

                    Pablo


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

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

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

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

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

                          - 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

                           

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

                             

                            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

                            • 26. Re: vCenter Database full after a few days
                              Hot Shot

                              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.

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

                                 

                                 

                                Best regards,

                                Pablo

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

                                  Hello,

                                  did you finally resolve your transaction log issue?

                                   

                                   

                                  Best regards,

                                  Pablo

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

                                    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!