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!

  • Article

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,

https://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