Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1831
  • Last Modified:

vcdb_log.ldf is getting too big.

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
Tiras25
Asked:
Tiras25
  • 9
  • 5
  • 4
  • +6
5 Solutions
 
QlemoC++ DeveloperCommented:
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
 
HumpdyCommented:
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
 
HumpdyCommented:
you should also see this article.

http://support.microsoft.com/kb/873235
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
QlemoC++ DeveloperCommented:
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
 
VirtaliciousCommented:
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
 
sarabhaiCommented:
If you need to reduce the size of log then  first take full database backup and then  truncate the log using log backup
0
 
Danny McDanielClinical Systems AnalystCommented:
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
 
Tiras25Author Commented:
Dan, I don't have rollup jobs under SQL Server Agent.  Am I looking into the right place?  Attached.
SQL-Server-Agent.jpg
0
 
Danny McDanielClinical Systems AnalystCommented:
Expand the Jobs folder and you can see if they've been running by double-clicking on the Job Activity Monitor
0
 
Tiras25Author Commented:
Yes looks like they've been running
Job-Activity-monitor.jpg
0
 
Danny McDanielClinical Systems AnalystCommented:
Looks healthy, so I would turn my attention back to the database and the previous comments.
0
 
SOMAguyCommented:
Got it.  Let me review the database and trunkate/shrink.
0
 
James MurrellProduct SpecialistCommented:
Also making the DB recovery option SIMPLE will do the needful
0
 
Paul SolovyovskyCommented:
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
 
Tiras25Author Commented:
Where do I change from Full to Simple?  
0
 
James MurrellProduct SpecialistCommented:
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
 
Tiras25Author Commented:
Thank you! And how can I view what I have now?
0
 
Danny McDanielClinical Systems AnalystCommented:
rt-click on the vc database, choose properties, then click on options.
0
 
Tiras25Author Commented:
Recovery Model: Bulk-logged.
0
 
Tiras25Author Commented:
If I switch it to Simple.  Do I need to stop the virtual center?  
0
 
QlemoC++ DeveloperCommented:
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
 
Tiras25Author Commented:
Okay done.  Now after I purge, truncate, and shrink the problem hopefully will go away...

Thank you.
0
 
Tiras25Author Commented:
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
 
QlemoC++ DeveloperCommented:
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
 
Tiras25Author Commented:
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
 
QlemoC++ DeveloperCommented:
No. The last backup time is showed in the "General" tab.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 5
  • 4
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now