Apparently one of the things that VMware’s support department will often recommend that people do is to change the recovery mode of the vCenter database within SQL Server from FULL to SIMPLE recovery because it will magically make a lot of disk problems go away. While this is true, your disk space issues on the vCenter database will go away at this point, this isn’t magic and you have have just lost some functionally that you weren’t expecting to loose.
The big thing that you loose when doing this is that you can no longer restore the SQL Server database to a specific point in time if you have a system failure. If you don’t need the ability to restore so a specific point in time, and restoring to your latest full or differential backup that you have (you do know that you need to backup the vCenter databases right?) then changing the recovery mode from FULL to SIMPLE is just fine. However if you do need that ability (and I would recommend that you have that ability so that if the vCenter server fails or someone deletes data you can get back as much trending data as possible) then you will want to leave the recovery mode as FULL, but instead setup transaction log backups (hopefully you have a DBA or a database consultant that can help you with this) so that they run every few minutes (I typically recommend every 10-15 minutes). Just keep in mind that when selecting how often they should run in the event of a server failure you may loose up to that amount of data from the database. So if you aren’t aloud to loose more than 10 minutes worth of data, don’t backup the log every 15 minutes, back it up every 10, or maybe every 8 just to be safe. Make sure that you get in writing from your management that this is the config that they want so if you get yelled at you are covered.
If you haven’t ever backed up the transaction log your disk is probably pretty full (or totally full). In this case you will want to shrink the database just once. But before you do you need to know what size you should shrink it to. This can be much easier if a DBA does it, but I’ve got faith in you to not screw it up.
First connect to the SQL Server using Management Studio. Take a backup of the transaction log to disk by right clicking on the database, selecting Tasks from the context menu then selecting Backup from the pop out menu. Select transaction log backup, set a file to backup the log to (you’ll need a lot of disk space for this) and make sure that there’s only one file listed in the file list then start the backup. This will probably take a while.
If you don’t care about loosing the log for now change the database from full to simple recovery and back. Do this by running this script.
[sql]
ALTER DATABASE vcdb
SET RECOVERY SIMPLE
GO
ALTER DATABASE vcdb
SET RECOVERY FULL
GO[/sql]
Now do a full backup as shown above (just do a full database backup instead of a transaction log backup).
Now with everything running as normal wait for how ever long it’ll be between transaction log backups. Then connect to the SQL Server in SQL Server Management Studio. Right click on the vCenter database (vcdb) and select Reports from the context menu, then “Disk Usage” from the list of reports. At the top it’ll give you the size of the transaction log in MB, and the graph on the right will show you a percentage breakdown of how full the transaction log file is. Take the Used percentage and double it. Now take that percentage and multiple it by the size of the transaction log. That’s the minimum size you should set your transaction log for. Personally I would round up to the 8 gig size above that size. If you need 150 Megs of log space, set the log for 8 Gigs in size. If you need 10 Gigs of log space, let the log for 16 Gigs of space. A little extra space used is fine, it is better for performance of the database if we don’t need to grow this transaction log. You can shrink the transaction log by using the DBCC SHRINKFILE statement as shown.
[sql]DBCC SHRINKFILE (‘vbdc_log’, 8388608)[/sql]
Keep in mind that the number, in this case 8 Gigs, is passed in in megs. Do NOT for any reason create any sort of scheduled task to regularly shrink any database file. This will do nothing good and will cause performance problems of its own. If anyone has told you to setup a job which shrinks the database file regularly they are WRONG.
Denny
