DPM 2012 - Shrinking of Transaction Logs and Best Practice RRS feed

  • Question

  • We currently are using DPM 2012 to back up our SQL server db’s. The issue we are facing with most of our larger databases, when restoring the databases to any other instance the transaction logs are extremely big, sometimes bigger that the data file itself.   Db’s are being backup at least hourly. It is my understanding that truncation will occur on the transaction log.  However, we would like to shrink the transaction file to allow us the restoring process easier. Is this a recommended course of action? When it will be best to perform? Should it be done manually, thru a SQL Maintenance Job or doing a DPM Pre-Backup/Post-Backup script? Any Best Practices by Microsoft for this task.

    Tuesday, April 23, 2013 8:32 PM

All replies

  • Hi

    You can use this SQL query against the DB in question to see how much free space is inside the log file.

    SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
    FROM sys.database_files;

    The way log truncation works is SQL marks transactions internally to the log file so the records can be reused.  SQL does not resize the log file after a backup.  The size that the log file gets is determined by: 

    A) The amount of activity of the SQL DB.
    B) the amount of time before it gets backed up.

    So as an example, let’s say a SQL log starts off at 1GB, and over a typical work day, the log grows to 20GB, and your 1st backup was at the end of the day, the log would remain 20GB forever.  If you took the 1st backup after an hour and backups every hour thereafter, then the log file would probably not grow larger than about 2.5GB  (20gb/8hr work day = 2.5GB per hour)

    So with DPM we can do incremental backup’s as often as every 15 minutes if you like, but you will need to get the LOG file back down to a reasonable size to start with manually if they are already large.  



    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Mike J. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, May 3, 2013 11:01 PM