SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Transaction Log file full errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shadba
Starting Member

4 Posts

Posted - 08/19/2013 :  15:55:17  Show Profile  Reply with Quote
We see log of errors in the SQL server ERROR.LOG file for a database. We are ruinning transaction log files backup every 1 Hr and the transaction log file size set to 5 GB. The log backup file size was never more than 2 GB. What may be the reason for these errors even though there is around 3 GB free space?
Thanks

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 08/19/2013 :  17:38:42  Show Profile  Reply with Quote
quote:
Originally posted by shadba

We see log of errors in the SQL server ERROR.LOG file for a database. We are ruinning transaction log files backup every 1 Hr and the transaction log file size set to 5 GB. The log backup file size was never more than 2 GB. What may be the reason for these errors even though there is around 3 GB free space?
Thanks

What are the errors that you are seeing? Can you post the exact text of a sampling of the messages?
Go to Top of Page

shadba
Starting Member

4 Posts

Posted - 08/20/2013 :  07:58:38  Show Profile  Reply with Quote
2013-08-18 08:10:58.11 spid107 The transaction log for database 'XXXXX' is full due to 'LOG_BACKUP'.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 08/20/2013 :  08:08:24  Show Profile  Visit jackv's Homepage  Reply with Quote
Is that the first error in the error chain?
Have you checked the memory space on the server?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

shadba
Starting Member

4 Posts

Posted - 08/20/2013 :  08:57:29  Show Profile  Reply with Quote
Total memory on the server is 16 GB and SQL server instance is set to use only upto 12 GB. There are couple of other databases in the instance but the errors are coming up for this and one other database. Both those databases are heavy usage. That is not the first in the chain. I see those errors since 08/04.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
808 Posts

Posted - 08/20/2013 :  14:01:53  Show Profile  Reply with Quote
It looks like either the transaction log backups are failing - or you are not backing up that databases log file. The error message is showing you why the transaction log is full for that database and the reason is that the log for that database has not been backed up.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 08/21/2013 :  01:50:19  Show Profile  Visit jackv's Homepage  Reply with Quote
If you are doing a log backup - could you post any other tasks hapening just before or after a transaction log backup? for example , such as shrinking

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

yelouati
Starting Member

10 Posts

Posted - 08/24/2013 :  00:47:46  Show Profile  Reply with Quote
No knowing enough about your environment. Quickest action I would take is set the DB recovery to Simple then back to full, then take a new DB backup. Also, check if your log file is allowed to expand. This would clear up the log and allow to see if some transaction is trying to go past the 5GB limit and rolls back.
Go to Top of Page

shadba
Starting Member

4 Posts

Posted - 08/26/2013 :  07:51:32  Show Profile  Reply with Quote
We are not doing any activities like shrinking around the time when it happend. For now i increased the maximum log file size limit to 15 GB and the current size is around 10 GB. After that change, we do not see any errors like that. Let me see if that erro comes back when the log file reaches 15 GB. In this matter, how can we determine log file size the application requires? If running the transaction logs backup more frequently make any change? In my case the transaction log file backup size was never more than 2 GB.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000