Cant shrink log file

  • Good day everyone,

    i noticed that the log file is consuming too much space; From 8174 MB, it already consumed 6172 MB; i tried shrinking the database and it shows that its successful; however once i refresh the page it still giving me the same numbers;

    how you could help me;

    thanks in advance,

  • Are you in full recovery mode?

    If so, then you need to be backing up the log file on a regular basis in addition to your full backups.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Its already in full recovery and the transaction log backup is already included in the database maintenanceplan;

    thanks

  • cute_lhen05 (2/25/2010)


    Its already in full recovery and the transaction log backup is already included in the database maintenanceplan;

    thanks

    What is the frequency of your Tlog backup?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • the back up runs everyday;

    is there anyway to minimize the size?

    thanks

  • If I am to understand you correctly, it appears that you only backup your transaction log once a day as part of your backup maintenance plan which also performs the full backup.

    You must setup a separate plan for your tlogs. To keep growth to a minimum you should backup the t-logs at least hourly. You're transaction log does not appear to have a "lot" of activity and thus you could do with hourly. As soon as the t-log is backed up, your log will reclaim space from the transactions that were a part of the backup.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • we use the database often thats why we dont schedule it hourly;

    is there another option? thanks

  • cute_lhen05 (2/25/2010)


    we use the database often thats why we dont schedule it hourly;

    is there another option? thanks

    A common practice is to backup the transaction log every 15 minutes. Understand that the transaction log is not a full database backup.

    As an example, I backup transaction logs every 15 minutes. Those backups are 1.5GB in size which would be 6GB after an hour. You have 6Gb after an entire day. I don't think you will have a problem in backing up your log every hour.

    Again, your transaction log backups should be a different maintenance plan than your full backup.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • To add to what Jason stated...

    Not that I recommend it, but if you're only going to backup the log once a day, then there's almost no sense in having the database in the FULL recovery mode because you'll only be able to recover to the last day's backup anyway. It would be better to backup once an hour, at least. If the data is actually valuable (it always is...), then I'd recommend doing the T-Log backups every 15 minutes as Jason suggests.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If we are backing up the TLog.

    Will it reclaim the space ?

    cant we use DBCC shrinkfile option to regain the space ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (2/26/2010)


    If we are backing up the TLog.

    Will it reclaim the space ?

    cant we use DBCC shrinkfile option to regain the space ?

    Reclaim the space within the file. Backing up the tlog does not shrink the file.

    You could use shrinkfile to regain the space from disk, however you need to be careful about doing that as a regular practice. If the logfile is growing to 8GB and then you shrink it everyday to 1Gb just to see it grow again, you are helping to create more io and log fragmentation than necessary. It is best to create a log file size based on usage patterns and need. Doing this avoids shrinks and growths.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 1 through 10 (of 10 total)

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