| Author |
Topic |
|
shadba
Starting Member
4 Posts |
 Posted - 2013-08-19 : 15:55:17
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-19 : 17:38:42
|
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? |
 |
|
|
shadba
Starting Member
4 Posts |
Posted - 2013-08-20 : 07:58:38
|
| 2013-08-18 08:10:58.11 spid107 The transaction log for database 'XXXXX' is full due to 'LOG_BACKUP'. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-08-20 : 08:08:24
|
| 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 |
|
|
|
shadba
Starting Member
4 Posts |
Posted - 2013-08-20 : 08:57:29
|
| 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. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-08-20 : 14:01:53
|
| 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. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-08-21 : 01:50:19
|
| 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 shrinkingJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
yelouati
Starting Member
10 Posts |
Posted - 2013-08-24 : 00:47:46
|
| 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. |
|
|
|
shadba
Starting Member
4 Posts |
Posted - 2013-08-26 : 07:51:32
|
| 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. |
|
|
|
|
Transaction Log file full errors