Questions regarding VMWare and the database backend

[thecoffeeguy]

Not sure if this is the correct place to ask, but I was hoping to get some feedback on the database backend piece that VMWare uses.

We have a 3 node ESX 3.0.1 cluster right now. We just migrated the database files from MSDE to a SQL Server 2005 SP1 server.

Since this migration has happened, i've been able to dig a little deeper into the database side of things for VMWare.

I've had some very curious things happen since this has moved from MSDE to SQL Server 2005. Specifically, my tempdb.mdf, I have seen skyrocket all the way to 95gigs.

I am trying to gather some more information on how VMWare uses the database and what happens with the transactions. This just seems awfully high for the tempdb. Today, some work was done and I saw the vcdb.ldf file go from 10mb to 4gigs after a couple VM's were created.

Is this normal?

ANyone have links to info on this? Things I should look for?

What should the recovery mode be set to? Right now, it is at simple.

What should the compatibility level be set to?

Really appreciate the help.

TCG

EDIT: Hmm...whos the owner of the database supposed to be?

[RParker]

The size of your database depends on the number of VM's, but 4Gb seems a little high for 3 nodes. But it depends on the initial size you set for the database. The recovery mode should be simple as outlined in the instructions for setting up the VC. The compatibility mode should be native.

The tempdb is a temporary workspace. Among other uses, SQL Server uses the tempdb for:

•	Storage of explicitly created temporary tables.
•	Worktables that hold intermediate results created during query processing and sorting.
•	Materialized static cursors.

SQL Server records only enough information in the tempdb transaction log to roll back a transaction but not to redo transactions during database recovery. This feature increases the performance of INSERT statements in the tempdb. In addition, there is no need to log information to redo any transactions because the tempdb is re-created every time you restart SQL Server; therefore, it does not have any transactions to roll forward or roll back. When SQL Server starts, the tempdb is re-created by using a copy of the model database and is reset to its last configured size.

By default, the tempdb database is configured to autogrow as needed; therefore, this database may grow in time to a size larger than desired. A simple restart of SQL Server resets the size of tempdb to its last configured size. The configured size is the last explicit size set with a file size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE statement. This article presents three methods you can use to shrink tempdb to a size smaller than its configured size

[thecoffeeguy]

Thanks. Appreciate it.

Something definitely does not feel right. I am not much of a DBA (Learning though) to fully get what is going on. Either way, I am trying to figure out a few things.

Just seems like the files are much larger than they need to be.

Any other suggestions?

Thanks.

EDIT: Oh, one thing I noticed that is odd. Looking at the properties for the VM database-> Files The "Log" file on autogrowh is set to restricted. What is odd when I try to switch it to unrestricted,even if I hit apply, close the properties, I open the properties backup and it sets it bakc to 'restricted.' Kinda screwy.

[RParker]

SQL 2005 SP2 may fix the issue, that is supported by the VC 2.5 release.

[jhanekom]

What version of VC are you using? VC 2.0.1 Patch 2 fixed some pretty severe database-related problems that - amongst other things - resulted in the symptom that you describe (bloated tempdb.)

I'd suggest upgrading to VC 2.0.2 Update 2 if you're not already on there.

[thecoffeeguy]

Appreciate it very much. We are upgrading VC to 2.0.2. Hopefully, that corrects the problem.

Also, anyone have any idea of how large this database can grow to? Anyone have any examples they would like to share? Just want to get an idea of what to expect for this particular database.

[jhanekom]

The following database sizing spreadsheet is quite elaborate and seems to work quite well: http://www.vmware.com/support/vi3/doc/vc_db_calculator.xls

Additionally, VC 2.5 now provides a basic database size estimate when you select the statistics level.