Open Transactions Stuck in the Log of SIMPLE RECOVERY MODE Database

Question

Open Transactions Stuck in the Log of SIMPLE RECOVERY MODE Database

MostInterestingMan

SSCertifiable

Points: 6291
More actions
May 2, 2011 at 10:46 pm

#389421

Hi,
I have a transaction log that has grown over 2GB on a SQL Server 2008 server and I can't dump the log. I tried leveraging BACKUP LOG WITH TRUNCATE_ONLY command but get a message that the log is in use and there is not much space. I also learned from another blog that BACKUP LOG WITH TRUNCATE_ONLY command only works on SQL server 2005.
Any help is appreciated. Thanks.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 15 posts - 1 through 15 (of 18 total)

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

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

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

forsqlserver SSCoach Points: 18902 More actions · Answer 2

Dear Gila Thanx...

Can we set the VLFs on our own wish...or May We know how much VLFs is currently using by our log file.

If yes:Will this number be same for all databases log file?;-)

Thanks

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

Please post new questions in a new thread, don't hijack other people's threads.

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

forsqlserver SSCoach Points: 18902 More actions · Answer 4

forsqlserver

SSCoach

Points: 18902

May 3, 2011 at 12:39 am

#1318895

Forgive Me..

Thanks

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 5

GilaMonster (5/2/2011)
http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Thanks Gail! The article is very informative.

I ran select log_reuse_wait_desc , * from sys.databases and my log_reuse_wait_desc is REPLICATION. We do have Transactional Replication on this database and it is the culprit. The Log Reader Agent is running so I am not sure why it's not marking the replicated portions of the log inactive for reuse? At this point I am not sure I can do anything but wait for the next CHECKPOINT.

Your thoughts.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

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

It could be that the log reader is falling behind. Start checking the replication agents' logs.

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 7

GilaMonster (5/3/2011)
It could be that the log reader is falling behind. Start checking the replication agents' logs.

I think it has. I will check the logs.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 8

you can use this command on SQL Server 2008 and 2008 R2 also,if you want to truncate the file just add new log file and restrict the first one to specific size after then size reached then second file will be use then take a log backup and then truncate it

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Syed Jahanzaib Bin hassan (5/3/2011)

you can use this command on SQL Server 2008 and 2008 R2 also,if you want to truncate the file just add new log file and restrict the first one to specific size after then size reached then second file will be use then take a log backup and then truncate it

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com[/quote%5D

What command?

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

Syed Jahanzaib Bin hassan (5/3/2011)
you can use this command on SQL Server 2008 and 2008 R2 also,if you want to truncate the file just add new log file and restrict the first one to specific size after then size reached then second file will be use then take a log backup and then truncate it

The database is in Simple Recovery. Hence any log backup will fail. You cannot take a log backup of a database in Simple Recovery model.

Even if it were possible, it would not help in the slightest, as the output of sys.databases shows the cause of the log not being reused is replication, not log backups. Hence the solution is to fix the replication, not flair around guessing wildly.

Oh, and BACKUP LOG WITH TRUNCATE_ONLY does not work on SQL 2008 or SQL 2008 R2, no matter how many log files there are nor whether there's been a log backup first, nor what recovery model the DB is in. It was deprecated in SQL 2005 and removed in SQL 2008

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

GilaMonster (5/3/2011)
Syed Jahanzaib Bin hassan (5/3/2011)
you can use this command on SQL Server 2008 and 2008 R2 also,if you want to truncate the file just add new log file and restrict the first one to specific size after then size reached then second file will be use then take a log backup and then truncate it
The database is in Simple Recovery. Hence any log backup will fail. You cannot take a log backup of a database in Simple Recovery model.
Even if it were possible, it would not help in the slightest, as the output of sys.databases shows the cause of the log not being reused is replication, not log backups. Hence the solution is to fix the replication, not flair around guessing wildly.
Oh, and BACKUP LOG WITH TRUNCATE_ONLY does not work on SQL 2008 or SQL 2008 R2, no matter how many log files there are nor whether there's been a log backup first, nor what recovery model the DB is in. It was deprecated in SQL 2005 and removed in SQL 2008

Edited to protect the "inocent". Or whatever.

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

MostInterestingMan (5/3/2011)
GilaMonster (5/3/2011)
It could be that the log reader is falling behind. Start checking the replication agents' logs.
I think it has. I will check the logs.

Could be anything from huge transactions to excessive VLFs to IO contention.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

Replication is a very complex topic. There are numerous tuning options you have if it isn't keeping up and that is causing the tlog growth. Probably best to get a professional in to help you - you can FUBAR things pretty easily where replication is concerned.

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 14

TheSQLGuru (5/4/2011)
Replication is a very complex topic. There are numerous tuning options you have if it isn't keeping up and that is causing the tlog growth. Probably best to get a professional in to help you - you can FUBAR things pretty easily where replication is concerned.

Thanks for the suggestion but I work in a small shop and we can't afford consultants.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends