Why SQL 2008/5 transaction log is taking so much more space compared to SQL 2000?

If you compared the size of transaction log file’s size between SQL 2000 and SQL 2005/8, you will surely have an interesting observation. Transaction log file size in SQL 2005 or 2008 is significantly larger compared to SQL 2000.

The reason of this difference is because Microsoft changed transaction log file’s logical architecture from SQL 2000 to SQL 2005/8. For example SQL 2000 does not log lock related information and SQL 2005/2008 does log these information.

For SQL 2008 and 2005, below operations are logged in a transactional log:

1. The start and end of each transaction.
2. Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
3. Every extent and page allocation or deallocation.
4. Creating or dropping a table or index

If you are wondering for more details of logical architecture of transaction log, I recommend visiting http://msdn.microsoft.com/en-us/library/ms180892.aspx.