Knowledge Base

The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
 
Search the VMware Knowledge Base (KB)   View by Article ID
 

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

Symptoms

  • The transaction log (.ldf ) of the VirtualCenter database may grow to an excessive size
  • The Windows event error states:

    Faulty Application: vpxd.exe

  • Cannot start the VMware VirtualCenter Server service
  • The Virtual Center vpxd.log may show an error similar to the following when connected to a Microsoft SQL database:

    ODBC error: (42000) - [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database '<database>' is full. Back up the transaction log for the database to free up some log space .

  • Verbose logging in the VirtualCenter vpxd.log may show an error similar to the following:

    [VpxdVdb] Failed to get next sequence number: "ODBC error: (42000) - [Microsoft][SQL Native Client][SQL Server]The transaction log for database 'VIM_VCDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases" is returned when executing SQL statement "update vpx_sequence WITH (ROWLOCK) set id = ? where name = ?"

Purpose

For troubleshooting purposes it may be necessary to examine the recovery model and the transaction logs of the database that VirtualCenter is using. This article provides an explanation of the different database recovery models on VirtualCenter and how to truncate the transaction logs of the VirtualCenter database.

Resolution

Many customers choose to deploy VirtualCenter Server with a Microsoft SQL Server database. SQL Server offers administrators a choice of recovery models, which is the primary factor that determines transaction log disk space requirements. The full recovery model is the default and has the potential to consume all available disk space if appropriate database maintenance is not performed. It is best practice to schedule regular backups of the database and transaction log to avoid unnecessary growth.

 

In some instances VirtualCenter may crash because all disk space is consumed by the transaction logs or not start because of a lack of transaction log space. In these cases if a full backup cannot be performed and there is a need to free up space to be able to get VirtualCenter to start, it is possible to 'commit' and then shrink the transaction logs. To commit and shrink the transaction logs, follow the appropriate procedure below.

 

Note: If this is a production database, backup the database after following these steps. When the transaction log is committed there is no replay available for any transactions in a disaster recovery scenario after the last full backup that was performed.
 

Microsoft SQL 2000

 
On a Microsoft SQL 2000 server:
  1. Log in to the Microsoft SQL 2000 Server as an administrator.
  2. Open SQL Enterprise Manager.
  3. Right-click the database that VirtualCenter is using.
  4. Click Properties.
  5. Click the Options Tab.
  6. Set the Recovery Model to Simple as follows:



  7. Click OK.
  8. Right-click on the database again.
  9. Click All Tasks>Shrink Database.
  10. On the Shrink Database window, click Files.
  11. Select the transaction log from the database file list. This appears in the list as databasename_Log as follows:





    The space used versus the space allocated displays. In general after you set the recovery model to Simple you see the majority of the space in the transaction log released.
  12. Ensure that the Compress pages and then truncate free space from end of file radio button is selected. 
  13. Click OK to shrink the log.
  14. Click OK, to perform a shrink the database, and close the wizard. 
Space is now freed on the server. If the VMware VirtualCenter Server service was not starting due to lack of space in the transaction log attempt to start the service again at this point.
 

Note:  If there is more than one transaction log for the database, perform the above steps for each of the logs.

 
 

Microsoft SQL 2005/2008

 
On a Microsoft SQL 2005/2008 server:
 
Note: Microsoft SQL Server Management Studio Express can be used for SQL server 2005 Express Edition. If it is not installed on the server, you must download and install it from the Microsoft Download Center. For Microsoft SQL 2008 Express edition, see Download Center.
  1. Log in to the Microsoft SQL 2005/2008 Server as an administrator.
  2. Open up SQL Management Studio.

    Note: If you are using MS SQL 2005, ensure to use MS SQL Management Studio 2005. Alternatively, if you are using MS SQL 2008, ensure to use MS SQL Management Studio 2008.

  3. Right-click the database that VirtualCenter is using.
  4. Click Properties.
  5. Click the Options link.
  6. Set the Recovery Model to Simple as follows:



  7. Click OK.
  8. Once this is complete, right click on the database again.
  9. Click Tasks>Shrink>Files.
  10. On the Shrink Database window select the file type as 'Log' .  The file name appears in the filename drop down as databasename_log as follows:



    The space used versus the space allocated displays. After you set the recovery model to Simple, the majority of the space in the transaction log released.

  11. Ensure that the Release unused space radio button is selected.
  12. Click OK on this window to shrink the transaction log.  
 
Space is now freed on the server. If the VMware VirtualCenter Server service was not starting due to lack of space in the transaction log attempt to start the service again at this point.
 
Note:  If there is more than one transaction log for the database, perform the above steps for each of the logs.
 
Also see Investigating the health of a vCenter Server database (1003979) and Determining where growth is occurring in the vCenter Server database (1028356)

Additional Information

With the database in simple recovery mode, regular backups of the transaction log are not required as they do not grow substantially in size. The downside of this model is that it does not keep a record of the transaction logs, and therefore if there is a failure of the database all changes made after the last full backup are lost. If recovery from a failure is a high priority than the full recovery model must be used. Ensure that regular maintenance is performed by backing up the database and transaction logs. This allows for recovery of all data up until the point of failure. 
 
More information on recovery models for SQL Server, see:
 
 
Note: The preceding link was correct as of May 18, 2012. If you find that the link is broken, please provide feedback and a VMware employee will update the link.

Note: Another method which can be used to manage the growth of transaction logs is to configure the autogrow option. Please engage your DBA in regards to the use of this option. For more information see Microsoft KB: http://support.microsoft.com/kb/315512

Tags

vcenter-database-size-growth vcenter-server-service-fails vcenter-sql-database-fills-disk database-transaction-logs shrinking-a-database

See Also

This Article Replaces

The transaction log (.ldf) for my VirtualCenter database on my MSSQL Server is growing very large (1002008)
vCenter service stops when trying to start it (2004511)
vcenter service stops randomly / The transaction log for database 'VCDB' is full (2004816)

Update History

10/11/2011 - Added symptoms of Windows event error. 09/11/2012 - Added link to download SQL express 2008 edition. 02/08/2013 - Added link to articles 1003979 and 1028356

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 113 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.
What can we do to improve this information? (4000 or fewer characters)
  • 113 Ratings
Actions
KB: