Microsoft SQL Server 2008 R2 Unleashed- P110:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1034 CHAPTER 31 Transaction Management and the Transaction Log when the database is in simple recovery mode or when you have never performed a full backup of the database. If the database is configured to use the bulk-logged or full recovery models so that the log is being maintained the reusable portion of the log prior to the MinLSN cannot be truncated or purged until the transaction log has actually been backed up. If the first VLF cannot be reused because it contains the MinLSN or it hasn t been truncated yet SQL Server needs to expand the log file. This is done by adding a new VLF to the end of the physical log as long as the log file is still configured to grow automatically . SQL Server can then continue writing log records to the new VLF. However if the log file is not configured to auto-grow a 9002 error is generated indicating that the log file is out of space. Certain conditions can cause log records to remain active preventing the MinLSN from moving out of the first VLF which in turn prevents the VLFs at the beginning of the physical log file from being reused. Some of the conditions that can lead to the log space not being reused include but are not limited to the following No checkpoint has taken place yet since the log was last truncated and the log records are needed for database recovery. A database or log backup is in progress. A long-running transaction is still active. Database mirroring is paused. For more information see Chapter 20 Database Mirroring. The database is the primary database for transactional replication and transactions relevant to the publications have not yet been delivered to the distribution database. For more information on replication see Chapter 19 Replication. A database snapshot is being created for more information see Chapter 32 Database Snapshots . If something is preventing the log from being truncated SQL Server 2008 provides information in the system catalogs to determine what is preventing log truncation. This .
