log file full with disk space | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

log file full with disk space

Hi!
I have a database whose data and log file size is kept in unrestricted mode and 10% growth.
The size of log file is around 40GB where size of data file is 6 GB.
I have around 10 GB left in my Disk.Now this morning when I checked the event viewer I got a error message that "Log file of databse is full..BackUp the transaction log "
What made the sql server throw this error.there is enough free space in hard disk
A better question is why are you letting your tlog grow so large? Are you backing it up on a regular basis?
What recovery model do you have? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
i have a full recovery mode and I backup log file daily.
So you have to backup log more frequently, said 2 hours.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Yes I knew this..But my concern is why I am getting the error when I have space in my disk for log file.I havent restricted the size of log file.It is kept unrestricted
I’m far from a SQL Guru but my guess might be that it wants the next chunk, 4 gig, to contiguous space and while you have 10 gig left, its too badly fragmented to use. (This is just a guess.)
You could have got that error because a transaction was running, causing the log to grow, but the transaction was not finished before the disk cap limit was reached. The error occurs, then then the transaction rolls back and freeing up all that space in the log. The point is, you might have 10 GB of free space available on the disk now, but how do you know that there was 10 GB of free space on the disk when the error occured? Either way, you should either be backing up your transaction log regularly or you should be in simple recovery model if logs are not important to you. Karl Grambow www.sqldbcontrol.com
]]>