Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

BE 2010 SQL Backups

Created: 08 Jun 2010 | 5 comments

Hi,

I am trying to backup my vCenter database (resides on a physical machine) which is set to recovery model Full.
I have 1 policy to so a Full machine backup and a Full backup of the SQL databases.
I then have another policy to do a Differential machine backup along with a Truncate log Backup of the SQL databases.

The job is successful though for whatever reason the actual database logfile does not reduce in size. It is still 20GB and growing.
Is there something I am missing here?

The Informational event on the vCenter server is as follows:

Log was backed up. Database: vCenter, creation date(time): 2009/10/29(14:32:41), first LSN: 40632:108742:1, last LSN: 40632:193815:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'vCenter_00__4971e9aa_e86a_49eb_86c6_76cdc34305c5_'}). This is an informational message only. No user action is required.

Is there anything else I need to do to make sure the database log file gets truncated?


Regards,

Terry

Comments 5 CommentsJump to latest comment

pkh's picture

When the SQL log is truncated, it is done internally in the logfile.  The space is not released.  You have to use the Query Analyzer to issue the DBCC SHRINKFILE command to compress the logfile.

Terry Sposato's picture

Ohh, so perhaps run a post script for the Log backup jobs in order to do this then?
Are you aware of any?

Terry Sposato's picture

I found this SQL script:

USE vCenter

GO
DBCC SHRINKFILE(vCenter_log, 1)
BACKUP LOG vCenter WITH TRUNCATE_ONLY
DBCC SHRINKFILE(vCenter_log, 1)
GO

Does that mean I can run this as a post backup job script and the logfile will always truncate?
By the way I ran the script above manually and it truncated the log fine. Down to 1MB from 20Gb :)

pkh's picture

BE post-command accepts either commands or batch files.  You would have to modify the script to make it into a batch file.  This script comprises SQL command and is meant to be run the Query Analyzer.  Only when you can run the batch file in a command prompt then you can use it as a BE post-command.

pkh's picture

Actually, you only need these commands

USE vCenter
GO
DBCC SHRINKFILE(vCenter_log, 1)
GO

You have already done the log backup using BE.