Post reply

need good SQL Server script to shrink log files

  • May 20, 2009 at 2:49 pm

    #209691

    does anybody have or can write a good script to shrink transaction log files to 50MB. which i will run every sunday. your helpo is really appreciated.

  • May 20, 2009 at 3:10 pm

    #997928

    Scheduling a shrink of your transaction logs every week is a mistake you really don't want to make. Review the article I link to in my signature which explains how to manage the transaction logs.

    Once you have reviewed that article - you should have a better idea of how to handle whatever problems you are currently encountering.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • May 20, 2009 at 4:38 pm

    #997996

    I agree 100% with Jeffrey Williams.

    Your transaction log grows to the size it needs to be in normal use. If you have utility jobs (e.g. Defragmentation) then this is still the case.

    If you shrink them, they will just grow again next time. Only probably with more operating system level file fragmentation.

    The only time you should shrink either .mdf (data), .ndf (more data), or .ldf (log) is where you have taken some exceptional action which you know has expanded them and you need to shrink them again.

    If you plan carefully, these occasions will be few and far between.

    Tim

    .

  • May 20, 2009 at 6:43 pm

    #998020

    but i cannot keep the database in simple recovery mode my database should be at all time b ein full recovery mode. i got a script from somewhere and it shrinks the log (ldf) file to 10.00 MB. but i want to restrict it to 50 MB only so where i change the value. actually i do not understand the script. let me know where to make applicable changes to shrink to 50 MB.

    use master

    DECLARE @Statement varchar (2000)

    SELECT @Statement = ''

    SELECT @Statement = @Statement + 'USE ?; '

    SELECT @Statement = @Statement + 'SELECT ''?''; '

    SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '

    SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) 0; '

    SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 51200,truncateonly); '

    SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) 0; '

    SELECT @Statement

    EXEC sp_MSforeachdb @command1=@Statement

    GO

  • May 20, 2009 at 8:47 pm

    #998040

    Please review the article in my signature and learn how to manage your transaction logs. Do not shrink the transaction logs on a regular basis. Doing so will cause file level fragmentation and performance issues as the log needs to grow.

    If you have to keep your database in full recovery model - you need to schedule frequent transaction log backups, not shrink the transaction log on a regular basis.

    And seriously, a 50MB transaction log is nothing. I have systems where the transaction log is more than 50,000MB, and that is to handle the batch processing done weekly and off hours. I backup the transaction log on this system every 15 minutes and the backups can be several gigabytes.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • May 21, 2009 at 12:22 pm

    #998466

    Thanks Jeffrey Williams ...

    for Link in your signature


    - Pradyothana     http://www.msqlserver.com

  • May 21, 2009 at 12:27 pm

    #998471

    pradyothana (5/21/2009)

    Thanks Jeffrey Williams ...

    for Link in your signature

    Really - you should thank Gail for the article.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • May 22, 2009 at 8:40 am

    #998898

    Run the DBCC shrinkdatabase command to shrink your database size. You can also use the DBCC shrinkfile option as well.However, as mentioned earlier, by the other respectable members, you should not follow this way to compress your log files as it is not the best practice. However, its really acceptable if you are doing it on a test database to test your own methods.

    Thanks & Regards,
    Sudeepta.
    http://twitter.com/skganguly

  • May 22, 2009 at 2:27 pm

    #999168

    hi folks,

    can any one explain me when i should use shrink database command.What exactly happens when i do that? Plz explain under what cases i have to go for this option in real time scenarios.

    Is this a best way to take a back up? like shrinking the transaction log and then take a backup? Let me know if i'm wrong?

    thanks for your help.

  • May 22, 2009 at 2:50 pm

    #999189

    Sam,

    You shouldn't run the Shrink Database command. That is the point that Jeffrey and Tim were trying to make in their posts.

    As mentioned the only time you may want to do a shrink is if you have done an out of ordinary action against the database, like archiving all but 2 years of data to another database and then deleting that data from the current database. Then you can do a shrink, but you will also need to rebuild/reorganize you indexes at that time as well.

    When in doubt don't shrink. If you have the space, don't shrink.

    Read this article, http://qa.sqlservercentral.com/articles/64582/

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 23, 2009 at 12:44 am

    #999280

    Aatish,

    There are plenty of articles on this site which explain clearly on managing transaction logs, you shud start wit Gail's article first, understand the pros n cons of doing what u intend to do and then proceed.


    Pradeep Singh

  • May 23, 2009 at 7:51 am

    #999319

    hi,

    better use third party tools like litespeed, its in build shrinked and backed up instead of going in the traditional way.

    Goodluck

    KingManjunath

  • May 26, 2009 at 8:40 am

    #999925

    Sam,

    You need to shrink the Transactional log file on requirement, mostly, when you are running out of space. However, it is adviceable that you should take a log backup before shrinking the log file. Also shrinking the log file to a higher percentage can impact your DB/application. You need to consider that as well.

    The initial question was something else, my answer respond to one of the many situation which can occur.:-)

    Thanks & Regards,
    Sudeepta.
    http://twitter.com/skganguly

  • May 26, 2009 at 8:57 am

    #999933

    You don't want to shrink the log as a general rule. The only time you shrink the log is when it has grown uncontrollably for some reason. Either you forgot to set up log backups, or a one-time event, like a large data load, caused it to grown.

    In other cases, i.e. normal use, this space is needed. Shrinking the log just means you'll grow it again. You want a stable log size.

    Set log backups, monitor the peak size of them, and set the log to handle this.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 14 posts - 1 through 13 (of 13 total)

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