cancel
Showing results for 
Search instead for 
Did you mean: 

BE 2010 SQL Backups

Terry_Sposato
Level 3
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
5 REPLIES 5

pkh
Moderator
Moderator
   VIP    Certified
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
Level 3
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
Level 3
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
Moderator
Moderator
   VIP    Certified
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
Moderator
Moderator
   VIP    Certified
Actually, you only need these commands

USE vCenter
GO
DBCC SHRINKFILE(vCenter_log, 1)
GO

You have already done the log backup using BE.