Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dba123
Yak Posting Veteran
90 Posts |
 Posted - 2006-03-05 : 23:39:01
|
| Every time I get this error, at different points of testing inserts and deletions on my table:The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databasesWhy do I keep getting this? All I'm doing is deleting several hundred thousand records and inserting them into a couple of tables. i shouldn't have to truncate my log every time or my application bombs out!sys.databases only gives me this info for log_reuse_wait_desc which does nothing for me:LOG_BACKUP |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-06 : 01:55:44
|
| Either your TLog is set to a fixed size (in which case consider setting it to extend automatically) or your disk is full.You could delete your records in smaller batches, which would then only log each batch, rather than all the records you are trying to delete in one go. However, for this to work you your database Recovery model will need to be set to SIMPLE or you will need to have TLog backups running sufficiently frequently that they will backup the TLog before it grows to the same size as the single delete would cause!!If you can't use either of these style workarounds then the only other thing I can think of, albeit tedious!!, is to export the "good" part of the table, truncate the table and then re-import the "good" part - this works particularly well if you are only trying to retain a small percentage of the rows in the table. (In fact just INSERT INTO/ing the good rows into a temporary table, dropping the original table and renaming the temp table would work well too)Kristen |
 |
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2006-03-06 : 21:57:09
|
| Hi,Please refer what Kristen suggested and read below KBs http://www.support.microsoft.com/?id=110139http://www.support.microsoft.com/?id=317375Andy DavisSql Shield Team--------------------------------------------SQL Server Encryption Softwarehttp://www.sql-shield.com |
|
|
|
|
|
|
The transaction log for database 'mydatabase' is f