Post reply

Transacation Log too big

  • February 9, 2009 at 6:26 am

    #73441

    Hello All,

    I am running rebuild indexes as a nightly job every night. I see that the transaction log has grown to 140G(the database is 14G, and the transaction log is almost 10 times), Is this because of the reindexing, how do I avoid this. One thing I am thinking is to run the rebuild indexes on a weekly basis. Any other advice on how to keep the size of the transaction log in check. I am using the following scripts to reindex

    --Re-indexes the specified database

    -- We don't use sysname because it might not be long enough.

    -- sysname is 128 chars, so we use double that.

    BEGIN

    -- Quote the database name with brackets

    DECLARE @quoteddbname nvarchar(256)

    set @quoteddbname = quotename( 'cor_online' )

    -- The outer EXEC is so we can do USE, not allowed in stored procs

    -- The inner EXEC does the actual reindex on each table in the

    -- specified database

    EXEC('

    USE '+ @quoteddbname +'

    DECLARE @sTableName sysname

    DECLARE PKMS_Tables CURSOR LOCAL FOR

    select table_name from information_schema.tables

    where table_type = ''base table'' order by 1

    OPEN PKMS_Tables

    FETCH NEXT FROM PKMS_Tables INTO @sTableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    select @sTablename = quotename(@sTablename, ''[]'')

    EXEC('' DBCC DBREINDEX ( ''+@sTableName+'') WITH NO_INFOMSGS'')

    FETCH NEXT FROM PKMS_Tables INTO @sTableName

    END

    CLOSE PKMS_Tables')

    END

    GO

    Thanks

    Shri

  • February 9, 2009 at 6:40 am

    #940428

    Back-up your transaction logs regularly and this will truncate them allowing for the free space to be re-used

  • February 9, 2009 at 6:59 am

    #940445

    shri_sastry (2/9/2009)

    Hello All,

    I am running rebuild indexes as a nightly job every night. I see that the transaction log has grown to 140G(the database is 14G, and the transaction log is almost 10 times), Is this because of the reindexing, how do I avoid this. One thing I am thinking is to run the rebuild indexes on a weekly basis. Any other advice on how to keep the size of the transaction log in check. I am using the following scripts to reindex

    --Re-indexes the specified database

    -- We don't use sysname because it might not be long enough.

    -- sysname is 128 chars, so we use double that.

    Shri

    Why do you do a full reindex every night? I can't believe that on a database this size you have so much fragmentation during the day that you need a full rebuld of all indexes.

    Also your comment about sysname makes no sense. A database name is stored as sysname in the sysdatabases table. So a name can never be longer than the maximum size for sysname. In fact a databasename cannot be more than 123 characters.

    [font="Verdana"]Markus Bohse[/font]

  • February 9, 2009 at 7:31 am

    #940474

    Thanks

    I think we will change the reindexing to once a week instead of once a day. This was on the test server where the transaction logs are not being backed up. but on the production server the transaction log is being backed up. Is this a good practice for reindexing once a week

    Change the recoverymode on the database to simple

    reindex

    change it back to Full recovery

    Can any one of you give me a good procedure to follow on reindexing and keeping the transaction log file size manageable

    Thanks

    Shri

  • February 9, 2009 at 11:07 am

    #940674

    shri_sastry (2/9/2009)

    Thanks

    I think we will change the reindexing to once a week instead of once a day. This was on the test server where the transaction logs are not being backed up. but on the production server the transaction log is being backed up. Is this a good practice for reindexing once a week

    Change the recoverymode on the database to simple

    reindex

    change it back to Full recovery

    Can any one of you give me a good procedure to follow on reindexing and keeping the transaction log file size manageable

    Thanks

    Shri

    a transaction log backup after reindex shud do the needful..

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • February 10, 2009 at 12:43 am

    #940997

    The best method would be to write a Maintenanceplan for doing all this.

    Sriram

  • February 10, 2009 at 6:33 am

    #941138

    I tried to build a maintenance plan, but I am getting the following error when I do it. I using the maintenance plan wizard in SQL 2005. If anyone can help me resolve this error

    Apply to Target Server failed for Job 'Maintenance Plan'

    Thanks

    Shri

  • February 10, 2009 at 10:20 am

    #941432

    shri_sastry (2/10/2009)

    I tried to build a maintenance plan, but I am getting the following error when I do it. I using the maintenance plan wizard in SQL 2005. If anyone can help me resolve this error

    Apply to Target Server failed for Job 'Maintenance Plan'

    Thanks

    Shri

    Its not necessary to do this thru a maintenance plan....I do this through a job ..... u can have a script scheduled for the same...

    Moreover...can u provide more detail i.e when u r getting dis error....during execution..or when saving......

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • February 10, 2009 at 10:36 am

    #941447

    http://www.sqlservercentral.com/scripts/Index+Management/

    Scripts for index management.

    Be sure you have transaction log backups running.

    The target server error is because you are working with master target servers. I'd disable this as it was flaky in 2000. Set up the maintenance plan on the server itself, not a master server.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply