locked
How can i reduce the intial size of the user database log file? RRS feed

  • Question

  • We have a database with log file intial size 60GB. Now we are facing the space issue we want to reduce the log file intial size. How can i do?

    1) Go to database properties and reduce the log file size.

    2) with help of the alter command.

    which option is best is their any other better options please advise me..

    Thanks,


    rup
    Friday, November 18, 2011 7:05 PM

Answers

  • If you set your db to FULL recovery model and did not perform log backups, then

    1) Set up db to SIMPLE recovery model

    2) run BACKUP LOG and then DBCC SHRINKFILE (dblog,100)

     

    If it did not help,please provide more info about configuration


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 20, 2011 10:55 AM
  • Hi Jeff

    In order to reduce log file size I suggested two options 1)Set to SIMPLE and 2) if the db is set to FULL then perform BACKUP LOG file. I think you just misunderstood my proposal... 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    That's completely different to what you posted. You have also not clarified why you told the OP to run DBCC SHRINKFILE on the log (Step 2).

    Your original post advises the OP that

    "If you set your db to FULL recovery model and did not perform log backups"

    then they need to do 1) and 2).

    1) tells the OP to set the recovery to SIMPLE

    2) tells the OP to backup the log file and run DBCC SHRINKFILE on the log.

    There's no way the OP would have known that 2) relates to having a recovery model of FULL and that 1) should not be done if they plan to do 2).


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Wednesday, November 23, 2011 6:51 AM
  • HI rupc !
    You may need to look at the following articles as it describes in details regarding shrinking;
    http://www.akadia.com/services/sqlsrv_shrink_logfile.html
     
    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham
    Saturday, November 19, 2011 10:55 AM
    Answerer

All replies

  • HI rupc !
    You may need to look at the following articles as it describes in details regarding shrinking;
    http://www.akadia.com/services/sqlsrv_shrink_logfile.html
     
    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham
    Saturday, November 19, 2011 10:55 AM
    Answerer
  • If you set your db to FULL recovery model and did not perform log backups, then

    1) Set up db to SIMPLE recovery model

    2) run BACKUP LOG and then DBCC SHRINKFILE (dblog,100)

     

    If it did not help,please provide more info about configuration


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 20, 2011 10:55 AM
  • This question would be better answered in the Database Engine Forum
    Monday, November 21, 2011 1:46 PM
    Answerer
  • If you set your db to FULL recovery model and did not perform log backups, then

    1) Set up db to SIMPLE recovery model

    2) run BACKUP LOG and then DBCC SHRINKFILE (dblog,100)


    If the recovery model is switched to SIMPLE and the database is backed up, there's no need to do a DBCC SHRINKFILE on the log.

    SIMPLE = No log backups.  Atomatically reclaim log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Wednesday, November 23, 2011 4:14 AM
  • Hi Jeff

    In order to reduce log file size I suggested two options 1)Set to SIMPLE and 2) if the db is set to FULL then perform BACKUP LOG file. I think you just misunderstood my proposal... 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 23, 2011 6:35 AM
  • Hi Jeff

    In order to reduce log file size I suggested two options 1)Set to SIMPLE and 2) if the db is set to FULL then perform BACKUP LOG file. I think you just misunderstood my proposal... 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 23, 2011 6:35 AM
  • Hi Jeff

    In order to reduce log file size I suggested two options 1)Set to SIMPLE and 2) if the db is set to FULL then perform BACKUP LOG file. I think you just misunderstood my proposal... 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    That's completely different to what you posted. You have also not clarified why you told the OP to run DBCC SHRINKFILE on the log (Step 2).

    Your original post advises the OP that

    "If you set your db to FULL recovery model and did not perform log backups"

    then they need to do 1) and 2).

    1) tells the OP to set the recovery to SIMPLE

    2) tells the OP to backup the log file and run DBCC SHRINKFILE on the log.

    There's no way the OP would have known that 2) relates to having a recovery model of FULL and that 1) should not be done if they plan to do 2).


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Wednesday, November 23, 2011 6:51 AM
  • "If you set your db to FULL recovery model and did not perform log backups"

     

    then they need to do 1) or 2).


    >>>ou have also not clarified why you told the OP to run DBCC SHRINKFILE on the log (Step 2).

    Because BACKUP LOG file does not reduce the size.....


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 23, 2011 7:27 AM
  • "If you set your db to FULL recovery model and did not perform log backups"

     

    then they need to do 1) or 2).


    >>>ou have also not clarified why you told the OP to run DBCC SHRINKFILE on the log (Step 2).

    Because BACKUP LOG file does not reduce the size.....


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 23, 2011 7:27 AM