VMware Cloud Community
Shanuvashd
Contributor
Contributor

Virtual Center SQL database log file is growing rapidly

In our company we have a virtualcenter 2.5 server which is used to manage 16 ESX 3.5 host . The SQL database files are kept in the C drive. The SQL transaction log file is growing rapidly from 100 MB to 16 GB. The SQL version is 2005. i use the SQL management studio console to shrink the database which brings down the log file size to 2 MB. but once i start the SQL and virtualcenter management services again, after few mins it starts to grow again. i could not find a permanent solution. Can someone help me on this? How to get rid of this frequent rapid transaction log growth?

Reply
0 Kudos
7 Replies
jkumhar75
Hot Shot
Hot Shot

For the permanent solution, you need to create the new database and specify the limits on that database to grow.

After creating the new database you can take a backpu of existing database and restore it on the new database.

By schedule downtime, point the VC to use the new database.

Also you can schedule the maintance jobs to delete the transaction log files from the database weekly or monthly.

Jay

VCP 310,VCP 410,MCSE

Consider awarding points for "helpful" and/or "correct" answers.

If you found this or other information useful, please consider awarding points for "Correct" or "Helpful". Jayprakash VCP3,VCP4,MCSE 2003 http://kb.vmware.com/
Reply
0 Kudos
a_p_
Leadership
Leadership

Not sure if this is a solution for you, however I usually set the recovery model of the database to "Simple" and create a maintenance task which backs up the database on a nightly basis.

André

Reply
0 Kudos
Troy_Clavell
Immortal
Immortal

I agree, "simple mode" is the way to go.

Keep this handy as well

http://www.vmware.com/files/pdf/vc_microsoft_sql_server.pdf

Reply
0 Kudos
Shanuvashd
Contributor
Contributor

The SQL database Model "Simple" only. Still the log grows rapidly. I get these errors in the event viewer

*The transaction log for database 'VirtualCenterDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases*

SQL Server Scheduled Job 'Past Day stats rollupVirtualCenterDB' (0x8BCD3EC056C65A4FBCC93F7790A1D618) - Status: Failed - Invoked on: 2010-07-01 20:00:00 - Message: The job failed. The Job was invoked by Schedule 1 (30 min schedule). The last step to run was step 2 (Delete past day rolled up records).

Reply
0 Kudos
a_p_
Leadership
Leadership

Setting the recovery model to "Simple" should actually not fill up transaction logs.

However in case you have open transactions this could happen.

Take a look at the following MS KB's:

Q873235 - How to stop the transaction log of a SQL Server database from growing unexpectedly

Q317375 - A transaction log grows unexpectedly or becomes full on a computer that is running SQL Ser...

André

Reply
0 Kudos
Shanuvashd
Contributor
Contributor

What is the use of "pastday stats rollup " sql job.

Disabling it solves the problem of transaction log growth.

is it ok to have this disabled?

Reply
0 Kudos
a_p_
Leadership
Leadership

The use of these SQL jobs is to manage historical data.

See KB1004382 - Updating rollup jobs after the error... for how to recreate these Jobs.

André