A delete statement in SQL Server 2000 takes around 1.5 GB of Transaction Log Space, Where as in SQL 2008 it can take around 3.2 GB!


There are a lot of Changes in the Transaction Log Architecture of SQL 2000 and SQL 2008. There is a huge difference in the way the log records are maintained in SQL 2000 vs SQL 2005/2008.



We keep more information in the Transaction Log in SQL 2005/2008. For example, we do not keep the LOCK information in SQL 2000, but in SQL 2005/2008 we keep that information in the log files. There are similar changes which increases the log size.


On a whole the architectural differences in 2005 & 2008 causes the increased log space compared to that of SQL 2000.


 


For more details, check out these useful links,


http://msdn.microsoft.com/en-us/library/ms180892.aspx (Transaction Log Logical Architecture)


 


 


Ashwin Menon


Support Engineer, Microsoft SQL Server


 


Reviewed By,


Akbar Farishta
Tech Lead, Microsoft SQL Server

Comments (1)

  1. Dinesh Vishe says:

    Good analysis.