Why is my transaction log full?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gail Shaw » Why is my transaction log full?

Add to briefcase

77 posts, Page 2 of 8

««1 2 3 4 5 »»»

Why is my transaction log full?

Expand / Collapse

Author

Message

Posted Monday, February 21, 2011 10:26 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:17 AM
Points: 1,786, Visits: 1,022

I like the idea!

Post #1067195

bitbucket-25253

Posted Monday, February 21, 2011 12:33 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,123, Visits: 20,370

Let me add my thoughts great article and thanks for the code examples.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #1067265

Posted Monday, February 21, 2011 1:03 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163

Great article, great reading.

Now.. how can I manually activate/deactivate VLF's within a transaction log?

Thanks,
S

--
Hehe

Hehe

Post #1067284

Posted Monday, February 21, 2011 1:23 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 38,099, Visits: 30,392

Slick84 (2/21/2011)

Now.. how can I manually activate/deactivate VLF's within a transaction log?

You can't. What are you trying to do?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1067296

Posted Monday, February 21, 2011 1:25 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 38,099, Visits: 30,392

Thanks everyone. Glad you're enjoying it.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1067298

Posted Monday, February 21, 2011 1:31 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163

It was just a question. I've seen that error before, I'll typically check the open trans and if/when the log was backed up.

It's most annoying when you see that error on a 'simple recovery' mode database. Specially common in QA/DEV DB's running on virtual machines.

--
Hehe

Hehe

Post #1067301

Posted Monday, February 21, 2011 1:48 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 38,099, Visits: 30,392

Slick84 (2/21/2011)

It was just a question. I've seen that error before, I'll typically check the open trans and if/when the log was backed up.

Which will only help you if the reason is log backup, active transaction or replication

It's most annoying when you see that error on a 'simple recovery' mode database. Specially common in QA/DEV DB's running on virtual machines.

Hence one of the reasons for writing the article and mentioning the possible fixes for the causes. Log backups (and hence recovery model) is just one of many reasons for a full log.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1067306

davoscollective

Posted Monday, February 21, 2011 6:20 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 413, Visits: 704

Thanks for the very informative article.

Running the first query I see Simple Recovery model and 'ACTIVE_TRANSACTION'
However, running DBCC OPENTRAN report "No active open transactions."

This doesn't seem to add up. My limited understanding is that the log still has an open transaction preventing reuse, but there aren't any transactions running.

Any pointers on what to do from here?

Post #1067371

Posted Monday, February 21, 2011 7:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 6:15 PM
Points: 8, Visits: 220

Hi Gail, Great article. However i have 1 question; Are you saying that when one issue the command backup log db_name with truncate_only it backs up the active portion and releases the space for re-use ? so the backup chain is not broken ?

Post #1067374

Posted Monday, February 21, 2011 7:29 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 1,560, Visits: 1,408

david.howell (2/21/2011)

Thanks for the very informative article.

Running the first query I see Simple Recovery model and 'ACTIVE_TRANSACTION'
However, running DBCC OPENTRAN report "No active open transactions."

This doesn't seem to add up. My limited understanding is that the log still has an open transaction preventing reuse, but there aren't any transactions running.

Any pointers on what to do from here?

Sys.databases isn't going to be updated as soon as the transaction completes. Try running a checkpoint in the database.

My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring

Post #1067378

« Prev Topic | Next Topic »

Add to briefcase

77 posts, Page 2 of 8

««1 2 3 4 5 »»»

Permissions

Expand / Collapse