October 7, 2003 at 2:15 pm
Hi All
I am running SQL 6.5 and have attempted to run a large query inserting a couple of thousand entries into a table. The transaction log became full and error 1105 appeared stopping all other queries.
I have tried to manually dump the transaction log but it doesn't appear to make any difference the error will not go away. I have also tried a complete re-boot of the machine still no joy. I would appreciate any suggestions even if they are things you think I would have already tried.
October 7, 2003 at 2:34 pm
First, try to identify whether data segment full or log segment full. From error message, you should have object id, If the object ID is not 8, data space could not be allocated, If the object ID is 8, log space could not be allocated.
If it indictaes data segment full, Use alter database to increase the size of the data segment, drop objects from the database or delete rows from tables in the database.
If it indicates that the log segment is full on the database, To clear space in the log, follow these steps:
1. Determine how many rows are in the syslogs table, as follows:
use database_name
go
select count(*) from syslogs
go
2. Dump the inactive portion of the transaction log using the dump transaction statement. If this statement fails with the 1105 error, retry the statement using the with no_log option.
3. Repeat step 1. If the number of rows in syslogs has decreased significantly, proceed to step 4. If not, an outstanding transaction is probably preventing the log from being cleared. If this is the case, restart SQL Server and repeat step 2. When SQL Server starts and the database is recovered, the outstanding transaction is rolled back, allowing the log to be cleared by a subsequent dump transaction statement.
4. If the dump transaction statement was executed using either the no_log option or the truncate_only option in step 2, dump the database now, because these options prevent subsequent changes recorded in the log from being used to recover from a media failure. You must run dump database to ensure the recoverability of subsequent database modifications.
October 8, 2003 at 2:43 am
In future it may be a good idea to Back up the DB - Then segment any large queries over different ranges. In between each of the mini inserts you can dump the transaction log.
October 8, 2003 at 6:27 am
If I remember right from when I used 6.5 you have to extend the log first to enable sql to commit/rollback trans to put the database in the right state. SQL cannot commit or rollback to a consistent state because it needs to write to the log file to do so, which is of course full, catch 22. By extending the log file you can get sql to tidy up to a consistent state and then backup your logfile/database accordingly. I have seen databases corrupted if not recovered correctly.
Far away is close at hand in the images of elsewhere.
Anon.
October 8, 2003 at 2:32 pm
quote:
Hi AllI am running SQL 6.5 and have attempted to run a large query inserting a couple of thousand entries into a table. The transaction log became full and error 1105 appeared stopping all other queries.
I have tried to manually dump the transaction log but it doesn't appear to make any difference the error will not go away. I have also tried a complete re-boot of the machine still no joy. I would appreciate any suggestions even if they are things you think I would have already tried.
Dumped Transaction log, extended data base segment. Syslogs less than 30 enteries therefore trasaction log not full. New error 'error minus 2147217900 can't allocate space for object in data base because the default segment is full'. Have you any idea how to increase the size of the default segment.
Thanks
October 8, 2003 at 2:49 pm
Create new device with command DISK INIT, extend default segment to new device using sp_extendsegment 'default', yournewdevice and expend database to the new device with ALTER Database command.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply