Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vcdb_log.ldf is getting too big.

Posted on 2010-11-22
26
Medium Priority
?
1,813 Views
Last Modified: 2012-05-10
My logs grew again but steady at 120GB.  I did a shrink again.  
vcbd.mdf is normal 3GB.

What's the normal size for the log file and why is it getting so large?  

Please advice.  Thank you.
0
Comment
Question by:Tiras25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 4
  • +6
26 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 34190119
If you could shrink it, that log space must have been needed at some point in time, within a single transaction. Maybe you are doing bulk inserts or deletes sometimes?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34190126
Hi,

The log file will grew as much as is SQL needs it to grow.
You should not shrink your log file.

You should set up a maintanance plan to schedule a regularl transaction log backup.
This will stop it from excessively growing.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34190139
you should also see this article.

http://support.microsoft.com/kb/873235
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 71

Expert Comment

by:Qlemo
ID: 34190274
I don't think you have your database in bulk-logged or full recovery mode - because then you would not have been able to shrink it.
However, if the DB is in full recovery mode, your transaction log backup is running to seldom. Try increasing the frequency of log backup.

I would not try and limit the autogrow - if the log file reaches that limit, the database will refuse any modify operation, since it cannot be logged anymore. This is until the DB has free space in the log file again, e.g. by performing a transaction log backup, or commit (in single recovery mode).
0
 
LVL 3

Expert Comment

by:Virtalicious
ID: 34190322
What level of logging are you doing?  Is it tuned for your environment?

Logging level can create a DB Size runaway in so many ways, so I would validate you didnt have an admin increase the logging level "just because"

Let us know!
-Virt
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 34190342
If you need to reduce the size of log then  first take full database backup and then  truncate the log using log backup
0
 
LVL 16

Expert Comment

by:Danny McDaniel
ID: 34190417
Take a look at the rollup jobs under SQL Server Agent and make sure that they are completing successfully.  I've seen where they weren't running for awhile and then when they do start running, they fill up the log trying to pare down the data.  If that's the case, you'll probably see that your vpx_hist_stat1 table is huge and you're gonna need to truncate the table or run a query to delete old entries.
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34190482
Dan, I don't have rollup jobs under SQL Server Agent.  Am I looking into the right place?  Attached.
SQL-Server-Agent.jpg
0
 
LVL 16

Expert Comment

by:Danny McDaniel
ID: 34190492
Expand the Jobs folder and you can see if they've been running by double-clicking on the Job Activity Monitor
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34190555
Yes looks like they've been running
Job-Activity-monitor.jpg
0
 
LVL 16

Expert Comment

by:Danny McDaniel
ID: 34190597
Looks healthy, so I would turn my attention back to the database and the previous comments.
0
 

Expert Comment

by:SOMAguy
ID: 34190633
Got it.  Let me review the database and trunkate/shrink.
0
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 800 total points
ID: 34190795
Also making the DB recovery option SIMPLE will do the needful
0
 
LVL 42

Expert Comment

by:paulsolov
ID: 34191785
I agree with making the recovery SIMPLE especially if you're using SQL 2005 Express. If you have it on FULL create a maintenance plan to shrink the logs, otherwise the 4GB limit will cause issues.
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34192167
Where do I change from Full to Simple?  
0
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 800 total points
ID: 34192189
switching to simple you will lose the ability to recover between full/differential backups.  You will not need to perform log backups, because sql will truncate the log automatically as soon as the transactions are committed.

The following will put your db into simple recovery: in sql query analyser
ALTER DATABASE dbname SET RECOVERY SIMPLE
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34192272
Thank you! And how can I view what I have now?
0
 
LVL 16

Assisted Solution

by:Danny McDaniel
Danny McDaniel earned 400 total points
ID: 34192279
rt-click on the vc database, choose properties, then click on options.
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34192314
Recovery Model: Bulk-logged.
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34192556
If I switch it to Simple.  Do I need to stop the virtual center?  
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 800 total points
ID: 34192650
As long as there is no open transaction, you can do it online and without restarting anything. The command resp. the GUI will tell you if you can do it.
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34192739
Okay done.  Now after I purge, truncate, and shrink the problem hopefully will go away...

Thank you.
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34198390
Okay I switched that to Simple Recovery Model.  Shrink it again.  Now it's down to 100MB.  Looks good.
We'll see how long it will stay this way.

Thoughts?
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 800 total points
ID: 34198435
As long as the log file does not grow beyond 1 GB, I would not shrink it again. The volume it will get by time will be needed.
You should be fine now with your settings.
0
 
LVL 17

Author Comment

by:Tiras25
ID: 34198500
Thank you.  Also you mentioned to increase the frequency of logs backups? I looked on all my vcenters and nothing selected there.  Am I looking in a right place?
Attached.  Transaction logs shipping
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 34199360
No. The last backup time is showed in the "General" tab.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If we need to check who deleted a Virtual Machine from our vCenter. Looking this task in logs can be painful and spend lot of time, so the best way to check this is in the vCenter DB. Just connect to vCenter DB(default DB should be VCDB and using…
When rebooting a vCenters 6.0 and try to connect using vSphere Client we get this issue "Invalid URL: The hostname could not parsed." When we get this error we need to do some changes in the vCenter advanced settings to fix the issue.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question