Sponsored Links

VMWare Virtual Centre SQL Transaction Logs

If you're running Virtual Centre to manage your guest virtual machines you will have a database running behind the system.  For many this will mean you have a copy of Microsoft SQL Server 2005 or 2008.

In the default configuration, new databases are created with Transaction Logging at Full.  This causes SQL Server to keep a record of every read, write, update and delete performed on the database.  The result of this is that you use up disk space very quickly.


For most people using Virtual Centre, there is no need to make use of the transaction facility at all and you can just run it off.  At the very least you can commit the transactions every now and then and shrink the database.  Here's how to deal with it.

To Turn Off Transaction Logging

  • Start up the SQL Server Management Studio and expand the databases tree until you can see the Virtualc entre database
  • Right click the database and select Properties
  • Select the Options page - top left
  • Change the Recovery Model to Simple and click OK
  • Now you will need to shrink the database to recover the used space
  • Right click the Virtualcentre database again and this time select Tasks > Shrink > Database
  • Click OK

Symptoms Of Insufficient Transaction Log Space

  • Virtual Centre may fail to start
  • You may see an error message like :
    ODBC error: (42000) - [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database '' is full. Back up the transaction log for the database to free up some log space .

In either case, take a back up of the database then follow the procedure for turning off transaction logging.

To Check The Size Of The Transaction Log

There are 2 methods to check this.

Check the actual files:

  • In Windows Explorer navigate to your SQL data folder. If you used default settings this is likely to be something like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
    It's worth pointing out this is probably a bad idea as you can cause problem starting Windows if you run out of disk space on the system volume
  • You should see 2 types of files - one ending in mdf, one ending in _log.ldf
    The mdf contains the data, the ldf file is the transaction log
  • You can just check the file properties for the size on disk

From SQL Server Management Studio:

Enter the following query
DBCC SQLPERF ( [ LOGSPACE ] | [ "sys.dm_os_latch_stats" , CLEAR ] | [ "sys.dm_os_wait_stats" , CLEAR ] ) [WITH NO_INFOMSGS ]

Back To Virtualization

Options