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.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
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?
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