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 «««45678»»»

Why is my transaction log full? Expand / Collapse
Author
Message
Posted Tuesday, March 15, 2011 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:26 PM
Points: 37, Visits: 466
Thanks Steve, The reason is I forgot that the default is full when the database is created. We had a small crisis when the log disk got full over the weekend. We didn't need point in time recovery so we backed up the db and then switched to simple but the transaction log was still large. We ended up using dbcc shrinkfile to reduce the size. After reviewing, I found that there are other databases that were apparently created the same way before I came along.
Post #1078489
Posted Tuesday, March 15, 2011 10:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
As long as you are sure you don't need point in time recovery, you did the right thing.

I would argue that most people don't think they need point in time recovery until something breaks. Then they are upset that all the work they did today is gone.

If you don't need it, fine, but be sure. Don't guess, and don't necessarily ask the question "how much data can you lose". Ask them how they feel if the db crashes 5 min before the next full.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1078493
Posted Tuesday, March 15, 2011 11:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
When switching to simple recovery model, you also lose the ability to perform file or filegroup or page level restores. For example, if I had a corrupted page in a database, I could restore the affected page from the full backup and then apply all of the log backups since that time to bring the page up to date.

Nonetheless, there are definitely times when simple recovery is fine to use so long as you are aware of the ramifications.




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 #1078543
Posted Monday, September 26, 2011 5:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
Thanks Gail, for detailed article. This is very useful and this goes to briefcase
Post #1180871
Posted Sunday, January 29, 2012 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 3, 2012 3:49 PM
Points: 3, Visits: 18
I'm having a hard time on where to start.. I'm a little overwhelmed.
Post #1243289
Posted Sunday, January 29, 2012 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 3, 2012 3:49 PM
Points: 3, Visits: 18
I'm having a hard time on where to start.. I'm a little overwhelmed.
Post #1243290
Posted Sunday, January 29, 2012 8:58 AM


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 @ 9:13 AM
Points: 40,609, Visits: 37,070
Feel free to post questions in the forums, we can probably help you figure things out.


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 #1243297
Posted Sunday, January 29, 2012 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 3, 2012 3:49 PM
Points: 3, Visits: 18

I appreciate the guidance and education.. Learning process for me. Thank you
Post #1243302
Posted Thursday, March 29, 2012 8:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 3:47 PM
Points: 3, Visits: 20
Hi,

We got this error:

-- Msg 9002, Level 17, State 4, Procedure sp_xxx, Line nn
-- The transaction log for database 'dbdbdbdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

It seems to have been caused by lack of physical space on the Server's drive in this case so I suggest check that as well.

Regards

Walter



Post #1275484
Posted Thursday, March 29, 2012 9:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
Running out of space on the drive is a symptom, not the cause. You need to look past the symptom to see what caused it. Possible root causes would be lack of log backups, long running transaction, or replication/CDC/mirroring not processing the transactions.



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 #1275487
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse