Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Why is my transaction log full? Expand / Collapse
Author
Message
Posted Monday, February 21, 2011 10:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:36 PM
Points: 1,786, Visits: 1,116
I like the idea!
Post #1067195
Posted Monday, February 21, 2011 12:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 5,618, Visits: 25,218
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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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


--
Post #1067284
Posted Monday, February 21, 2011 1:23 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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.



--
Post #1067301
Posted Monday, February 21, 2011 1:48 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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
Posted Monday, February 21, 2011 6:20 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:07 PM
Points: 460, Visits: 864
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 9:11 AM
Points: 8, Visits: 241
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:20 AM
Points: 1,616, Visits: 1,544
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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1067378
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse