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>
Hi,
how many ESXi hosts and VMs do you manage with your vCenter?
Have you changed the default logging levels of vCenter?
+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.
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...
and here the other settings:
Settings should be fine like this right?
schepp 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!
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
no offence Borja_Mari
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
Edit: Just saw that my Transaction log is again filled up to 9.9 GB after 3 hours (!!). VCenter was idle and i just was logged in with vSphere Client (doing nothing).
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:
This should fix your issue
Best regards,
Pablo
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!
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:
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)
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.
and here the other settings:
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.
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
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?
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.
Did you guys consider an alternate solution of using the vcenter appliance?
Regards
Girish
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.
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...
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
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.:
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...
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