Transaction Log

Hi Experts,
I need your help. My transaction log is being truncated automatically eventhough the recovery model of the database if FULL and the AutoShrink is turned off. Any idea?
LVL 11
Salim FayadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
May I ask what the problem is? Normally people complain that the transaction log grows too large.

Salim FayadAuthor Commented:
I need the transaction log since I am reading from it to see what got changed in the database.
Máté FarkasDatabase Developer and AdministratorCommented:
If the recovery model is FULL and the AutoShrink is off your transaction log will not truncated automatically. It will grow until the disk is full. If you take a FULL or transactional backup of the database then the space in the log will become free but the size will not decrease (not shrinked). You have to consider the followings:
1. Switch to SIMPLE recovery model
2. Regularly make full and/or transactional backups
3. Regularly truncate the log and shrink it (not recomended)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.


Is there any SQL schedule configured for either backing up transaction log or database maintenance?
FemSteenkampIT managerCommented:
Salim FayadAuthor Commented:
  1. Why do I want to set the recovery mode to "Simple"? I need the transaction log, that's why I set it to "Full"
  2. Regularly make full and/or transactional backups: why???
  3. Regularly truncate the log and shrink it: why???
paritoshjani: No, there is no scheduled job for transaction log. The truncation is being done around each 20 mins (sometimes 5 mins, other times 30 mins)
FemSteenkampIT managerCommented:
what are you using to read from teh log file?

is the actual .ldf file shrinking? or just the data you are reading from it?
Salim FayadAuthor Commented:
I am using the fn_dblog to rad from the transaction log.
As for the .ldf file, I guess not. But I cannot be sure since the intervals are very small.
what's recovery database?
what's recovery model of database?
Salim FayadAuthor Commented:
I already mentioned that the recovery model of the database is "Full"
can you check maintenance & backup history?
Salim FayadAuthor Commented:
After we did a restart on the server, it worked properly. Can anyone explains this?
Salim FayadAuthor Commented:
It is happening again. Any ideas?
FemSteenkampIT managerCommented:
the problem is probably the limitation of fn_dblog, that it reads limit amount of data and teh way it interacts with the virtual log files.

Can you provide teh code snippet of how you read from the log
 fn_dblog(null,null)   ??? and what data is missing, older or newer data from teh log?

Salim FayadAuthor Commented:
Here is my query:
SELECT t1.[Operation], 
		t1.[Slot ID], 
		, t1.[Transaction ID], 
		tBegin.[Transaction Name], 
		tBegin.[Begin Time], 
		tEnd.[End Time], 
		t1.[RowLog Contents 0], 
		t1.[Page ID], 
                  (SELECT COUNT(cvalue) - 1 from dbo.fn_split(T1.AllocUnitName,'.'))
                  WHEN 1 THEN
                        RIGHT(T1.AllocUnitname, LEN(T1.AllocUnitname)
                        - CHARINDEX('.', T1.AllocUnitname))
                  WHEN 2 THEN
                        LEFT(RIGHT(T1.AllocUnitname, LEN(T1.AllocUnitname)
                        - CHARINDEX('.', T1.AllocUnitname)),
                        -1 + CHARINDEX('.', RIGHT(T1.AllocUnitname,
                        - CHARINDEX('.', T1.AllocUnitname))))
            END) AS [TableName]
	FROM master.dbo.fn_dblog(null, null) AS T1
		INNER JOIN master.dbo.fn_dblog(null, null) as tBegin
			ON tBegin.Operation = 'LOP_BEGIN_XACT'
			AND t1.[Transaction Id] = tBegin.[Transaction Id]
		INNER JOIN master.dbo.fn_dblog(null, null) as tEnd
			ON tEnd.Operation = 'LOP_COMMIT_XACT'
			AND tEnd.[Transaction Id] = tBegin.[Transaction Id]
		AND t1.AllocUnitName NOT LIKE 'sys%'
		AND t1.AllocUnitName <> 'Unknown Alloc Unit'
		AND (CONVERT(datetime, tBegin.[Begin Time]) BETWEEN @STIME AND @ETIME)
		AND (CONVERT(datetime, tEnd.[End Time]) BETWEEN @STIME AND @ETIME)	
		AND t1.AllocUnitName NOT LIKE '%MSmerge_%'
ORDER BY [End Time]

Open in new window

FemSteenkampIT managerCommented:
yup as suspected, from my testing

the function only returns the latest data from the last active virtual logfile.

run this:
select * from fn_dblog(null, null)
--do so trans
select * from fn_dblog(null, null)   -- you will see the new trans in output
checkpoint   -- forces flush of dirty data into log, and moves all open trans to "active" portion of log
select * from fn_dblog(null, null)

this will now on only show a few transactions.

it this is the same for you, then i doubt whether you can use the function to scan th complet log file

Salim FayadAuthor Commented:
Thanks FemSteenkamp. Tomorrow, I will check it.
Salim FayadAuthor Commented:
FemSteenkamp, no it didn't work.
could it be from the replication? We have a replication of type "Merge". Could it be affecting the transaction log?
Salim FayadAuthor Commented:
The problem was that I was doing a "Shrink". After doing that, I had to take a backup of the database in order to keep the TransactionLog. It is like to remake its "Recovery Mode" to "Full"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Salim FayadAuthor Commented:
I solved it myself
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.