Post reply

Shrink DB using Maintenance Plan does not affect the log file size

  • February 9, 2009 at 2:35 am

    #205186

    Dear All,

    I need to reduce the log file size...

    I've created a maintenance plan for shrinking the DB in my local machine, I've created every thing ok and scheduled it to work daily. and when I right click and then execute, the dialog displays successful process, but when I check the DB Log, it still huge size (about 20 Gb). I do not know why the log file size still huge after shrinking the DB.

    Any help, plz???

  • February 9, 2009 at 2:44 am

    #940318

    firstly, try to check whats the percentage of the log file that is in use by

    DBCC SQLPERF(LOGSPACE)

    or do like below for your database in non peek hours...

    USE AdventureWorks;

    GO

    -- Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE AdventureWorks

    SET RECOVERY SIMPLE;

    GO

    -- Shrink the truncated log file to 1 MB.

    DBCC SHRINKFILE (AdventureWorks_Log, 1);

    GO

    -- Reset the database recovery model.

    ALTER DATABASE AdventureWorks

    SET RECOVERY FULL;

    GO

    Sriram

  • February 9, 2009 at 2:59 am

    #940326

    sriram, :D;)

    Thank you very much...you solve it.

    But how can we make this automatic... I mean can we do this by maintenance plan? because some times we forget to reduce the log file size, and I know that we can make this automatic by building a plan in maintenance plan section in SQL 2008.

    Regards

  • February 9, 2009 at 3:23 am

    #940335

    It's not really a good idea to have a job that Shrinks your database daily., this will cause massive fragmentation and will cause the database to run slowly. You are far better off giving your database enough room to grow.

    To truncate your Log file you should setup transaction log backups

  • February 10, 2009 at 3:00 am

    #941038

    I completely agree with steveb's comments.

    You should only shrink a database file if you know there will be a permanent reduction in the space needed.

    If you think a database file will grow again within 3 months you should not shrink the file. If you do have repeated shrink and growth of database files it will badly harm your performance.

    There are lots of forum postings on this subject. It is worth using Google, etc, to find them and take the time to understand what they are saying.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • February 10, 2009 at 6:45 am

    #941151

    Please be aware that setting your DB to simple and back to full again will break your log chain and you must take a full backup to start it over.

    Tim White

  • February 10, 2009 at 7:00 am

    #941167

    What do you mean by this process (setting DB to simple then back it to Full again) will break the Log chain and I should take a full backup to start it over?

    I've set the DB to simple, then back it to full again, but I do not understand you :ermm:, what exactly should I do now?? :blink::doze::sick:

  • February 10, 2009 at 7:06 am

    #941174

    Look at these posts to find out how the transaction log works and how to maintain it. These should answer all of your questions.

    http://qa.sqlservercentral.com/articles/Design+and+Theory/63350/

    http://qa.sqlservercentral.com/articles/64582/

  • February 10, 2009 at 7:08 am

    #941176

    When you have a DB in full recovery model, you take periodic transaction log dumps. this process allows you to restore your database to a point in time. A DB in the simple recovery model cannot do this. So, you have your full backup, then transaction log backup 1, 2, 3, etc..... When you set your DB to simple recovery model, This tells SQL Server to automatically truncate your log after each transaction is completed. No tran backups allowed.

    So, when you set the DB back to a recovery model of full, the old transaction log dumps are no good. Further, you cannot take a transaction log dump until you have taken a new full backup.

    Tim White

  • February 10, 2009 at 7:13 am

    #941179

    The articles rlondon points out are far better than my simple explanation. It would do you a lot of good to read through those thoroughly. we posted at approximately the same time....

    Tim White

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

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