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)

Note: This post is first blogged @http://blogs.msdn.com/b/sqlserverfaq/archive/2009/08/29/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.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s