SQL Server Performance

Why log file is >than data file

Discussion in 'General DBA Questions' started by aaronsandy, Jul 18, 2005.

  1. aaronsandy New Member

    Initially I created a new db on remote server with restricting data file to 100MB and log to 100MB.I have created db objects and inserted records from my local server via DTS.The data size on localserver was 60mb.I am astonished to see the size of log file,to my utter surprise it was full. I am wondering why the log file was full when I have inserted only 60mb of data.
  2. Ray D New Member

    Hello!
    Transaction log contains various operations. DML operations, extent allocations, various operations with indexes. Not only data. So, log can contain more data than DB.
    What kind of recovery model do you use?
    If you don't need to be able to restore DB to any point of time, you can use Simple recovery model. Read the following article in the BOL: Selecting a Recovery Model.

    WBR, Vlad A. Scherbinin
  3. ghemant Moderator

    as more and more logged transaction is done , t-log size would increase , please read :<br /><br /><b><a href='http://www.dbazine.com/sql/sql-articles/mullins-sqlserver' target='_blank' title='http://www.dbazine.com/sql/sql-articles/mullins-sqlserver'<a target="_blank" href=http://www.dbazine.com/sql/sql-articles/mullins-sqlserver>http://www.dbazine.com/sql/sql-articles/mullins-sqlserver</a></a></b><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Regards.<br /><br /><br />hsGoswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  4. aaronsandy New Member

    I havent done any other transaction apart form moving the 60MB of data from local server to remote server.Even then the loc file became full which was of 100mb
  5. FrankKalis Moderator

    As Ray D already mentioned, the log records not only data modifications, but also the corresponding actions on indexes and so on. So, inserting 60 MB into a table with some nonclustered indexes will result in a logfile +60 MB.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page