VMware Cloud Community
PeterLageri
Enthusiast
Enthusiast
Jump to solution

SQL Log file grows to limit, but it can be shrunk and used space is listed as 1%.

Hello.

3 ESXi Hosts 5.1.0, 1463097 in a cluster.

MS SQL 2008 R2 server (SQL Server 10.50.2500) on a VM MS Windows 2008 R2 Standard server.

Instance name VIM_SQLEXP

DB name: VIM_VCDB.

Recovery model: Simple

DB growth by 1MB unrestricted. Current Database size: 3887MB

Log growth by 10% restricted to 4GB max. Current size after shrink this morning 2MB BUT

Every morning the log file has almost run full but the VMware VirtualCenter Server service is running and I can start the vSphere Client. I know if I don't shrink the log the service will stop later today or tomorrow.

I have studied and followed

Determining where growth is occurring in the VMware vCenter Server database (1028356),

SQL Server Recovery Model Affects Transaction Log Disk Space Requirements (1001046)

Troubleshooting transaction logs on a Microsoft SQL database server (1003980)

but I can't see any table growing that much. I can see that  VPX_EVENT grew about 28MB over night but nothing else looks alarming.

Any ideas what it could be?

Best Regards

Peter Rasch Lageri

Denmark

1 Solution

Accepted Solutions
Borja_Mari
Virtuoso
Virtuoso
Jump to solution

Hello,

some time ago i faced similar situation than you.

I would recommend you to check carefully this, maybe it will be helpful:

Troubleshooting when the vcenter db transaction log becomes full (MS SQL server 2005 express)

Best regards,

Pablo

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob

View solution in original post

8 Replies
DanielOprea
Hot Shot
Hot Shot
Jump to solution

At that level you have the VC Statistics?

PLEASE CONSIDER AWARDING any HELPFUL or CORRECT answer. Thanks!!
Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA. ¡¡Muchas gracias!!
Blogs: https://danieloprea.blogspot.com/
Reply
0 Kudos
PeterLageri
Enthusiast
Enthusiast
Jump to solution

Statistics Level 1 on all.

and while we are at that, Task and Events are retained for 60 days both.

Best Regards

Peter

Reply
0 Kudos
DanielOprea
Hot Shot
Hot Shot
Jump to solution

Ok,

Please, aplly this KB:Purging old data from the database used by VMware vCenter Server 4.x and 5.x (1025914)

PLEASE CONSIDER AWARDING any HELPFUL or CORRECT answer. Thanks!!
Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA. ¡¡Muchas gracias!!
Blogs: https://danieloprea.blogspot.com/
Reply
0 Kudos
DanielOprea
Hot Shot
Hot Shot
Jump to solution

Also look at you the following KB

Investigating the health of a vCenter Server database

Creating a Maintenance Plan for SQL Server 2008 R2 for vCenter/UpdateMgr

BBDD  Best Practices

PLEASE CONSIDER AWARDING any HELPFUL or CORRECT answer. Thanks!!
Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA. ¡¡Muchas gracias!!
Blogs: https://danieloprea.blogspot.com/
Reply
0 Kudos
PeterLageri
Enthusiast
Enthusiast
Jump to solution

I have done the first one earlier. The Event Viewer Application log stated that the full log file was the reason that the service stopped.

Management plans are not available in that version of SQL Express.

Best pratices could be interesting but the installation is a rather small one, 3 hosts and 11 VMs.

Best Regards

Peter

Reply
0 Kudos
DanielOprea
Hot Shot
Hot Shot
Jump to solution

Ok, upload the diagnostic logs of VC

PLEASE CONSIDER AWARDING any HELPFUL or CORRECT answer. Thanks!!
Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA. ¡¡Muchas gracias!!
Blogs: https://danieloprea.blogspot.com/
Reply
0 Kudos
Borja_Mari
Virtuoso
Virtuoso
Jump to solution

Hello,

some time ago i faced similar situation than you.

I would recommend you to check carefully this, maybe it will be helpful:

Troubleshooting when the vcenter db transaction log becomes full (MS SQL server 2005 express)

Best regards,

Pablo

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
PeterLageri
Enthusiast
Enthusiast
Jump to solution

BINGO!

That solved it! Thank you. I had data from january this year which was double the amount expected. Now it's down 60 days as set in the GUI.

GREAT. Standing ovations are in order here.

Absolutely Best Regards

Peter

Reply
0 Kudos