Transaction log not clearing at Checkpoint

Question

Post reply

Transaction log not clearing at Checkpoint

Chris.Hubbard4U

Ten Centuries

Points: 1020
More actions
August 14, 2015 at 9:23 am

#398900

I have a Database that is set to Simple Recovery mode.
It is not clearing the log and the transaction log is growing.
I thought that it would clear the log upon checkpoint?
How can I force the Transaction Log to clear since NO-TRUNCATE is no longer available?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

I'd guess that you have a query still executing which is preventing the log from being cleared

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

1) are you SURE it is in SIMPLE recovery mode?

2) I think it won't clear until reaches end of current VLF.

3)

SELECT log_reuse_wait_desc

FROM sys.databases

WHERE name = 'MyDB';

4) Long-running transaction?

dbcc opentran

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Check the value of log_reuse_wait_desc. It tells you why VLFs couldn't be marked reusable last time SQL tried. There are many reasons why the log might not be cleared in simple recovery model.

Backup log with truncateonly would do nothing, even if it still existed. The only log reuse wait reason that would clear is log backup, which you can't get in simple recovery anyway.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris.Hubbard4U Ten Centuries Points: 1020 More actions · Answer 4

DBA From The Cold (8/14/2015)
I'd guess that you have a query still executing which is preventing the log from being cleared

Yes but I'm committing the transactions. Thanks.:-)

Chris.Hubbard4U Ten Centuries Points: 1020 More actions · Answer 5

TheSQLGuru (8/14/2015)
1) are you SURE it is in SIMPLE recovery mode?
2) I think it won't clear until reaches end of current VLF.
3)
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDB';
4) Long-running transaction?
dbcc opentran

It is definitely in Simple Recovery mode. Thanks.:-)

Chris.Hubbard4U Ten Centuries Points: 1020 More actions · Answer 6

GilaMonster (8/14/2015)
Check the value of log_reuse_wait_desc. It tells you why VLFs couldn't be marked reusable last time SQL tried. There are many reasons why the log might not be cleared in simple recovery model.
Backup log with truncateonly would do nothing, even if it still existed. The only log reuse wait reason that would clear is log backup, which you can't get in simple recovery anyway.

Thanks Gail.

It is just a test Server.

I am the only one doing anything on it.

Thanks.:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Chris.Hubbard4U (8/17/2015)
GilaMonster (8/14/2015)
Check the value of log_reuse_wait_desc. It tells you why VLFs couldn't be marked reusable last time SQL tried. There are many reasons why the log might not be cleared in simple recovery model.
Backup log with truncateonly would do nothing, even if it still existed. The only log reuse wait reason that would clear is log backup, which you can't get in simple recovery anyway.
It is just a test Server.
I am the only one doing anything on it.

Which doesn't change anything that I said.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris.Hubbard4U Ten Centuries Points: 1020 More actions · Answer 8

GilaMonster (8/18/2015)
Chris.Hubbard4U (8/17/2015)
GilaMonster (8/14/2015)
Check the value of log_reuse_wait_desc. It tells you why VLFs couldn't be marked reusable last time SQL tried. There are many reasons why the log might not be cleared in simple recovery model.
Backup log with truncateonly would do nothing, even if it still existed. The only log reuse wait reason that would clear is log backup, which you can't get in simple recovery anyway.
It is just a test Server.
I am the only one doing anything on it.
Which doesn't change anything that I said.

Sorry. 🙁 Thank you very much for your input.

I recall updating a table that would force transaction of type 0?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Updating data, if not in an explicit transaction, will be in what's called autocommit mode. An implicit transaction started as the statement starts and automatically committed when the update finishes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass