SQL Server Performance

Transaction log shipping + Size of Transaction log file growth after maintenance plan

Discussion in 'SQL Server 2005 Replication' started by davida37, Aug 26, 2008.

  1. davida37 New Member

    Hi,
    Please help ...We're using SQL Server 2005 Standard Edition. We have a production db and ship and restore logs onto a standby server. I generate the transaction log files nightly (when no activity on the db). During the week - there is no issue with the size of the transaction log files ( approx 2 GB - 150Mb rar'd down). We place these on an SFTP server to be picked up by the remote standby server.
    There is an issue with the size of the Transaction log files after maintenance plan is ran (every weekend) - this includes DBCC INDEXDEFRAG on indexes fragmented enough. The db is approx 30GB.
    Testing today I did an DBCC INDEXDEFRAG on 1 table's clustered index (largest table - approx 80m rows). This created 6GB Transaction log file. This did not include any days db activity. The db was in Full recovery mode. I have also just tested this with the db in bulk-logged recovery mode - and the transaction log files generated were exactly the same size. i.e. made no difference. I did not kick sql server after making this change. If maintenance is ran on the whole db - transcation log file growth is in excess of 50GB.
    Most recommendations say to put the db into bulk-logged recovery mode whilst doing housekeeping on db - but this didnt seem to have deired affect. Do I need to reconfigure the log ship jobs for bulk-logged recovery mode to kick in?
    thanks - David
  2. satya Moderator

    Welcome to the forums.
    The behaviour of size of log is by default as you are using INDEXDEFRAG which requires the place to play.
    First question I would like to ask is do you have any issues with disk free space on the server?
    If not then you can set a optimum size to transaction log such as to 10GB or 15GB in addition to what you have for the data file.
    If you are performing housekeeping for all the indexes in the database then the size of transactions log will be doubled, so if you can select only required indexes to be reindexed you can reduce this to some exent.
  3. davida37 New Member

    thanks for the prompt reply.
    disk space isnt the issue and there is ample disk space to play with (100gb + ) but we need to keep the size of the t log files to a minimum. The main issue is shipping the large t log files. We ship these out of our core network and bandwidth is very poor.
    Please note this is a datamart which we replicate into everynight - the transaction log files from this datamart are then shipped on. We are going to try the following :
    - Get rid of any redundant/non business critical indexes.
    - See if we can minimise index fragmentation in the replication routine by sorting on Clustered indexes befor e insert records into tables
    - Implement a maintenance script which checks for fragmentation over a set threshold. Also implement a safe guard which only re-builds say 1 or 2 indexes a night - and keep a log of these - so no indexes are missed over a weekly/monthly period.
    Any other suggestions would be greatly appreciated.
    thanks - David
  4. rohit2900 Member

    David,
    I would like to add few more things as the backup file size doesn't necessarily same as the db size. This means lets suppose you have set the data and log files to 10 GB each, so it doesn't mean as whenever you will be taking full backup that backup file size will be 20 GB...No its not like that and you can try this in your DEV db... SO setting up the size of .ldf file doesn't means that while taking a transaction log backup the backup file size will increase as the transaction log backup only includes all the committed transaction since last backup.
    As per BOL:
    A continuous sequence of log backups is called a log chain. Alog chain starts with a full backup of the database. Usually, a new logchain is only started when the database is backed up for the firsttime, or after the recovery model is switched from simple recovery tofull or bulk-logged recovery.
    Taking regular transaction log backups is necessary. In addition toletting you restore the backed-up transactions, a log backup truncatesthe log to remove the backed up log records from the log file. If youdo not back up the log frequently enough, the log files can fill up.
    Tell me if this helped.

Share This Page