I am doing some research into monitoring transaction log use using windows perfmon.

In the AdventureWorks database I have created the following statement

SET IDENTITY_INSERT Person.BusinessEntity ON

WHILE 1 = 1

    SELECT @id = MAX(BusinessEntityID) FROM Person.BusinessEntity
    SET @id += 1

    INSERT INTO Person.BusinessEntity(BusinessEntityID, rowguid, ModifiedDate) VALUES (@id,NEWID(), GETDATE())

    INSERT INTO [Person].[Person]

009-01-07 00:00:00.000')

The query will indefinitely enter rows into Person.BusinessEntity and Person.Person until I click stop

This should cause the transaction log to grow quickly which I can monitor in perfmon

I have added the perfmon counter for percent log used and run the statement and I get the graph as follows

enter image description here

I am wondering why the graph goes up and down again?

I assume it goes up when the log is written to and back down as the log resizes again?

If this is true then disabling autogrowth on the transaction log in the Adventure Works database and running the statement whilst monitoring again should see the graph go up and up and then reach the top and the query terminate because it is unable to resize the log?

enter image description here

However, I get similar behaviour again.

Can someone explain to me why am not seeing the transaction log start to fill after turning off autogrowth? Is there something else happening here?

The database is in full recovery mode

  • Is your database in SIMPLE recovery mode? – Scott Hodgin Mar 1 '18 at 10:04
  • Apologies, forgot to add that to the question. It's in full recovery mode – SEarle1986 Mar 1 '18 at 10:07
  • Did you ever take full backup of this db? – sepupic Mar 1 '18 at 11:40
  • Please update your question with the result of select last_log_backup_lsn from sys.database_recovery_status where [database_id] = db_id ('AdventureWorks'); – sepupic Mar 1 '18 at 12:42

My suspicion is that your database, while showing it's in FULL recovery mode, is actually in pseudo-simple mode where the database still behaves like it is still in SIMPLE recovery mode until a full database backup is taken.

Paul Randal, CEO of SQLSkills.com, wrote a blog post about this recovery model called pseudo-simple. This is a behavior of the database that, while it may be configured to be in FULL recovery model, still behaves as if it was still in SIMPLE recovery model. You can easily test this by creating a database and setting it to FULL recovery model. While you can perform a lot of transactions in the database, the transaction log will get truncated, as it is with databases in SIMPLE recovery model.

Paul wrote a TSQL function that can determine if a database is truly in FULL recovery. Here's the script from the above post.

USE [msdb];

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'SQLskillsIsReallyInFullRecovery')
    DROP FUNCTION [SQLskillsIsReallyInFullRecovery];

CREATE FUNCTION [SQLskillsIsReallyInFullRecovery] (
    @DBName sysname)
    DECLARE @IsReallyFull BIT;
    DECLARE @LastLogBackupLSN NUMERIC (25,0);
    DECLARE @RecoveryModel TINYINT;

        @LastLogBackupLSN = [last_log_backup_lsn]
        [database_id] = DB_ID (@DBName);

        @RecoveryModel = [recovery_model]
        [database_id] = DB_ID (@DBName);

    IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)
        SELECT @IsReallyFull = 1
        SELECT @IsReallyFull = 0;

    RETURN (@IsReallyFull);

And here is an example of utilizing that function (replacing 'test' with your database name).

select MSDB.DBO.SQLskillsIsReallyInFullRecovery ('test')

There is also a Powershell script written by Edwin Sarmiento that provides the same information:

Check If a SQL Server Database Is In Pseudo-Simple Recovery Model Using Windows PowerShell

My suggestion would be to take a FULL backup of the database and retry your tests.

  • I knew the concept, but had never heard of pseudo-simple. Great answer! – DimUser Mar 1 '18 at 13:12
  • This has fixed it. The query returned 0, then I did a full backup and then the query returned 1. Thanks, I've never known this behaviour – SEarle1986 Mar 1 '18 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.