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

The transaction log for database is full. Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 5:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:18 AM
Points: 44, Visits: 91
Hi Guys,

The following error i am getting when i insert or update and delete operation on database. please suggest me how can i solved this ASAP.

The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
Post #1505605
Posted Thursday, October 17, 2013 5:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
What is the recovery model ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1505611
Posted Thursday, October 17, 2013 5:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:18 AM
Points: 44, Visits: 91
Full.
Post #1505614
Posted Thursday, October 17, 2013 5:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
and do you have tlog back scheduled ? because if transaction log has not been backed up recently, backup might be what is preventing log truncation

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1505619
Posted Thursday, October 17, 2013 5:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 2,397, Visits: 2,936
When running a database in FULL recovery mode it is required to take regular LOG backups. Without running LOG backups the space inside the LOG-file can not be re-used and results in a full file (when max growth is set) or results in a full disk.
FULL recovery is required if you need to be able to restore the database to a specific point-in-time. If this requirement is not necessary for your database, consider changing the recovery model to SIMPLE.

So, schedule regular (every 15 - 180 minutes depending on the activity of the database) LOG backup or change the recovery model of the database to SIMPLE.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1505620
Posted Thursday, October 17, 2013 9:07 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 @ 8:19 AM
Points: 40,208, Visits: 36,617
Take a read through these: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
http://www.sqlservercentral.com/articles/Administration/64582/



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 #1505764
Posted Thursday, October 17, 2013 9:39 AM


SSC-Dedicated

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

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
Read the articles Gail mentioned.

As noted, you need log backups. The transaction log will keep all data around on transactions until it is backed up (some exceptions for mirroring or replication), so you need log backups.

If you aren't sure how often to run log backups, ask someone how much data you can lose in a disaster. They will say none, but that costs $$$. many orgs can survive an hour or two of data loss, but whatever the number, that's the frequency of your log backups.

We have a great script here to help with this that is better than maintenance plans, but you can use a maintenance plan if you like. Make sure you are only doing the log backups for those databases with full recovery model.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1505784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse