SQL Server – Monitor Transaction Log Percent Used in Perfmon

sql serversql server 2014transaction-log

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
BEGIN

    DECLARE @id INT
    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]
               ([BusinessEntityID]
               ,[PersonType]
               ,[NameStyle]
               ,[Title]
               ,[FirstName]
               ,[MiddleName]
               ,[LastName]
               ,[Suffix]
               ,[EmailPromotion]
               ,[AdditionalContactInfo]
               ,[Demographics]
               ,[rowguid]
               ,[ModifiedDate])
         VALUES
               (@id,
               'EM',
               0,
               NULL,
               'Ken',
               'J',
               'Sánchez',
               NULL,
               0,
               NULL,
               NULL,
               NEWID(),
               '2

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

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

Best Answer

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];
GO

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

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

    SELECT
        @LastLogBackupLSN = [last_log_backup_lsn]
    FROM
        sys.database_recovery_status
    WHERE
        [database_id] = DB_ID (@DBName);

    SELECT
        @RecoveryModel = [recovery_model]
    FROM
        sys.databases
    WHERE
        [database_id] = DB_ID (@DBName);

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

    RETURN (@IsReallyFull);
END;
GO

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.

Related Question