SQL Server Performance Forum – Threads Archive
Why log file is >than data fileInitially 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.
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
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 />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami<br />
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
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. —
Microsoft SQL Server MVP
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)