European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: MS SQL Server :: SQL-Server transaction log file growing infinitely, filling in the disk !!!
By: Nono	Date: 13/04/2005 09:28:30	Points: 20	Status: Answered Quality : Excellent
How to fix this bug ? When the transaction log fills the disk, then SQL-Server will stop serving the database (at least for updates and such) and complain about the lack of space ... How to fix this bug ? I've never seen this occur with any other serious database engine (Oracle, DB2, MySql, PostgreSql) Usually, the logfiles are maintained, cleaned up after each COMMIT, rotated, etc...
By: VGR	Date: 13/04/2005 09:44:23		Type : Answer
hu hu hu :D According to Microsoft, it's not a bug, it's a "feature" ;-))) See http://support.microsoft.com/?kbid=873235 As for the "why?", it seems from reading Merdu$oft's "explanations" that they simply didn't make it right ;-) As far as I understood, *even* when SQL-Server committed transactions, it will fill in the transaction log and will *not* "shrink" it unless YOU issue the command "DBCC SHRINKFILE" (T-SQL statement) Also, if you didn't give the log file a maximum size, then it takes the whole disk space ;-) *BUT* if you ever "bound" it, then you'll probably hit the wall as well, because even when truncating the log, it is possible to fill the log if it is too small for [that] single large transaction which in substance mean that *even* when truncating explicitly the log, you will fill in the log and may end up with an error telling you that you have to truncate the log :D This is indeed the case, ass seen in Microsoft's item 814574 : PRB: Error message: "Cannot shrink log file ..." occurs when you shrink the transaction log file Who said : "Kafka" ? ;-))) It's no wonder SQL-Server is not adopted worldwide ;-) I wish you a LOT OF LUCK to be able to "trucnate", "shrink" or otherwise "manage" your transaction log file, especially since SQL-Server CAN NOT HAVE TRANSACTIONS TURNED OFF ;-)) (unlike MySql where you can choose a transactionnal, or not, database engine ;-)

By: Nono	Date: 14/04/2005 09:47:07		Type : Comment
groumph :) We now suddenly have the same error on an other server !!! Microsoft OLE DB Provider for SQL Server error '80040e14' The log file for database 'cms' is full. Back up the transaction log for the database to free up some log space. /pLogin.asp, line 49

By: VGR	Date: 22/04/2005 17:09:31		Type : Comment
it's the same kind of problem.

By: Nono	Date: 28/04/2005 08:16:06		Type : Comment
many thanks ;-)

By: dschat	Date: 08/12/2005 13:06:04		Type : Comment
Have you backing up the log? Normally when you do that, you free up a lot of space. The best thing to do is schedule a job that checks the used logspace and when you reach a certain set level it automatically runs a t-log back up.

By: abhi_k007	Date: 05/04/2006 12:23:55		Type : Comment
hi Even i had this problm.. take the backup of transaction log regularly after u have the backup with you the then only u will be able to run Update queries as SQL will not allow u to delete the transaction log straight away plz revert back if the problm still persists.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 728.754040 milliseconds

Why Google AdSense ads ?

New Improved Search!