October 9, 2002 at 8:04 am
I have a database that is utilized only for the import and reporting of data from other servers. Needless to say there is no reason for me to have to restore this database in case of a crash because of packages that can repopulate it in a few minutes. My problem is this, last night an import did not occurr because of the following error message:
Error string: The log file for database 'macola_reporting' is full. Back up the transaction log for the database to free up some log space.
My question is this, if I have the database set to Simple, why is my transaction log filling up? I thought in Simple mode the database did not log transactions. Can someone tell me where my ignorance is apparent. 🙂
Thanks.
October 9, 2002 at 8:45 am
It is my understanding that SIMPLE still logs transaction, but it it will truncate the log when the log is 70% full or the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option. Check out BOL "Checkpoints and the Active Portion of the Log"
Also note that active portions of the transaction log can not be truncated.
I'd suggest allocating enough log space to handle a fair amount more than your largest transaction.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 9, 2002 at 8:46 am
When the database is in simple mode, it still uses the transaction log to ensure integrity. It just deletes the transaction after everything is safely written to the data portion of the database.
Hence if you have a large transaction, the log file can grow but will get truncated after.
Steven
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply