SQL Server Performance

Changing the log initial size

Discussion in 'SQL Server 2005 General DBA Questions' started by Pugnax, Jan 27, 2007.

  1. Pugnax New Member

    Folks:

    Is there a way to decrease the initial size of a database/log file? I've noticed you can increase it, but if you decrease it, after you confirm the change and go checking again, you will see nothing happened.

    Is there a way? Am I missing anything?!
  2. MohammedU New Member

    If you want to change the initial size of the db/log files, you have to change the model database...
    When ever you create a db is is the copy of the model...

    Once the db/log files created, if you want to shrink you have to use DBCC SHRINKFILE command...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. Pugnax New Member

    Thanx Mohammed...

    The problem is: the initial sizes in model are 3mb for data files and 1mb for log files. Still, some databases kept growing, and their initial size defaults are now changed to some weird values (the last size data/log had reached, probably), and all I can do is to increase them - whenever I try to reduce their size (in the initial size option), it doesnt work.

    Since their default is big, to shrink them just get them back to those (big) sizes. I've tried to shrink them, after changing their recovery model to simple, and run a full backup. In vain.

    Any clue?
  4. mmarovic Active Member

  5. Pugnax New Member

    mmarovic,

    Thanx, but this thread goes much further than what I need or have in mind. It's more about data archiving (and recovery) strategy than simply reducing file sizes. It shows how guys here are read good, though. Nice thread!!

    My question is far simpler: how do I reduce a log/data initial size to a lesser value?

    I've tried some of the commands stated in this thread (alter database modify file) and got sth like "MODIFY FILE failed. Specified size is less than current size.". I was wondering if there is sth less radical than "create database ... for attach_rebuild_log", which recreates the log file according to the model db size.
  6. mmarovic Active Member

    The point of the thread is: Do not reduce db log size. You said:
    quote:
    The problem is: the initial sizes in model are 3mb for data files and 1mb for log files. Still, some databases kept growing, and their initial size defaults are now changed to some weird values (the last size data/log had reached, probably), and all I can do is to increase them - whenever I try to reduce their size (in the initial size option), it doesnt work.
    Databases kept growing because they need more space. Reducing the space you gain nothing. Anyway if you think you have to shrink the db log, next thread may help you:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018&whichpage=1

    It is about sql server 2000, but I guess it should work on 2005 too.
  7. satya Moderator

    One of the reasons it is unable to SHRINK is due to the open transactions and not being empty, so you have to be clear about other jobs and processess that are running contributing this type of information.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. Pugnax New Member

    Thanx mmarovic!

    This really helped. Using a slight variation of what is used in that thread solved the problem:

    DBCC SHRINKFILE ('bd_log', 1 , TRUNCATEONLY) -- 1 or any other size in mb here.

    The point is: the databases were already there, when I've come to the company (small size, just beginning). They were created with recovery model full (default, right), but it's a non-critical DB in the development environment (read data mess), so the simple recovery model is more than enough, with weekly full backups and daily differential ones. Since this DB was in full mode and has never been backed up, you can figure out how big the log file is. I'm trying to fix this, and get the log back to its original size.

    Thanx you and Satya!
  9. satya Moderator

    Have you checked DBCC OPENTRAN for more information on open transctions, during the SHRINK operations.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  10. MohammedU New Member

    When the recovery model is full... I don't think daily differential backup is any good for you at all, because simple recovery model will not support poin in time recovery...

    As Satya mentioned check the if there are any open transactions...

    Read the following articles which help you to understand and shrink log file...

    How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
    http://support.microsoft.com/kb/907511

    How to stop the transaction log of a SQL Server database from growing unexpectedly
    http://support.microsoft.com/kb/873235

    http://www.codeproject.com/useritems/truncate_log_SQL_server.asp

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  11. Pugnax New Member

    Yes Satya, it returned "No active open transactions." - before shrinking, dont worry <img src='/community/emoticons/emotion-1.gif' alt=':)' />. <br /><br />The weird behaviour was SQL Server to have changed the initial size of both log/data to the biggest size it has achieved. The person who created the database (at the time) hasnt changed either model db, or the database defaults. Still, log/data files initial sizes were set to the last actual data/log file sizes.
  12. Pugnax New Member

    Thanx Mohammed, I am gonna check them out!
  13. satya Moderator

    You need not worry about sizes for Model & other system databases, Model is used at first time when the database is created with default settings and anyhow you have to set the values once it is created.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  14. MohammedU New Member

    I forgot to mention...
    If you don't specify the size of the db in your create statement db will be created as the same size as MODEL
    where as if you specify the size higher than model db size then db size will be over written with your specified size...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  15. mvijayal New Member

    Satya,
    I came across your article on sql-server-performance.com.

    I am a entry level DBA ,and the log file size of one of the critical production databases is currently growing at an alarming rate and is about 20GB.

    Don't know what is happening.

    I tried the following (from the article)

    alter database db
    set recover simple
    go
    dbcc shrinkfile (db_log, 100)
    go
    checkpoint
    go
    dbcc shrinkfile (db_log, 100)
    alter database db
    set recovery fullgo


    When I am executing this, I am getting the message as:

    Cannot shrink log file 2 (ris_log1) because all logical log files are in use.
    DBCC execution completed.Please contact your system admin.

    Also, I ran dbcc opentran(db) and found no errors.

    what am I doing wrong.

    still the log size is the same.
    no change..

    Please help.

    Thanks,
    Meena.
  16. MohammedU New Member

    Run the DBCC OPENTRAN command to see if there is any open transactions...
    If there are any replication transactions pending you can't shink the log file...if the db is part of replication...

    Why you want to be restrictive with shrink of database files
    http://www.karaszi.com/SQLServer/info_dont_shrink.asp?


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  17. satya Moderator

    Not only replication if there are any queries that are still running with maximum batch size such as updating more than 100,000+ rows at a time or deleting any such records will have open transaction, this will not allow the SHRINK operation to interact and until unless the statements are COMMITTED the log will be in same size. So its always better to perform a SHRINK during less traffic hours, but before that follow as suggested in the article & also the replies in thsi post.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page