Error 1105

Severity

17

Message text

Can't allocate space for object '%.*s' in database '%.*s' because '%.*s' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment.

Explanation

This error occurs when Adaptive Server is unable to allocate space in a database. The state of the error indicates what type of space could not be allocated:

1105 errors are written to the error log only when they occur during the checkpoint process. When a user process encounters this error, the message is returned to the client application without being written to Adaptive Server’s error log.

To correct the error, you must know:

• The Adaptive Server version level.

• Whether the error occurred during run time or during recovery:

  • Run time is a period during which Adaptive Server performs normal daily activities, such as receiving a query from a client application, processing a transaction, or returning results from a transaction.

  • An Adaptive Server is in recovery when doing activities such as restarting, performing the processing that occurs after a database dump is loaded, and performing the processing that occurs after a transaction dump is loaded.

• The state of the error.

Action

Use the sections below to correct the error, depending on whether the error occurred at run time or during recovery.

The data segment is full on the indicated data base. To obtain more data space, do one or both of the following:

• Use alter database or sp_extendsegment to increase the size of the data segment.

• Drop objects from the database or delete rows from a table.

The log segment is full on the indicated database. To clear space in the log, perform these steps:

1. Determine approximately how many pages the transaction log occupies using the commands:

   1> use <database_name> 
   2> go
   

   If the version is 12.5.x:

   1> select data_pgs (8, doampg)
   2> from sysindexes where id=8
   3> go
   

   If the version is 15.0.x:

   1> select data_pages(<database id>,8,0)
   2> go
   

   Where “8” is the ID for syslogs. The result is the number of data pages (2K pages on most operating systems) that the transaction log occupies.

   The query results may be inaccurate by as many as 16 pages, but using the query is much faster than counting the rows in the transaction log.

2. Dump the inactive portion of the transaction log using the dump transaction command. If this command fails with the 1105 error, retry the command using the with truncate_only option. If this command also fails with the 1105 error, run dump transaction with no_log.

   Using dump transaction with no_log can result in an 813 error. Therefore, do not use dump transaction with no_log unless it is absolutely necessary (when dump transaction with truncate_only will not truncate the log).

3. Repeat step 1. If the transaction log occupies significantly fewer pages, continue with step 4.

   If, however, there are still a large number of pages in the syslogs table, an outstanding transaction is probably preventing the log from being cleared. If this is the case, choose one of these options:

   • Refer to the section “Detecting and clearing long-running transactions” in Chapter 4 in the most recent version of the Troubleshooting and Disaster Recovery guide.

   • Restart Adaptive Server and repeat step 2. When Adaptive Server starts and this database is recovered, the outstanding transaction is rolled back, allowing the log to be cleared by a subsequent dump transaction. Depending on the size of the outstanding transaction, rolling back the transaction during startup recovery may be significant.

4. If the dump transaction command was executed with either the no_log or the truncate_only options 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 do a dump database now to ensure the recoverability of subsequent database modifications using transaction logs.

   This database dump is not required if your backup and recovery procedures do not include saving the transaction logs for media failure recovery.

   Do not assume that the occurrence of 1105 errors automatically means your transaction log is too small. If the data and the log are on the same segment, either the state 1 and state 2, or the state 3 actions may free enough space without increasing the size of the transaction log.

   If you are concerned that your transaction log is not large enough for your Adaptive Server, refer to “Creating and Managing User Databases” in the System Administration Guide: Volume 2 and read the section “Estimating the Transaction Log Size” before deciding to increase the log size. If appropriate, refer to Reference Manual: Commands for more information about the alter database command used to increase log size. Remember that once they have been increased, log and database sizes cannot be decreased.

The above procedures may not be effective in resolving the 1105 error due to excessive data growth in mixed data and log segments; you may need to move the data to a different device in this situation.

You may see 1105 errors when the log is full (states 3 and 4) on a database that is replicated or has been loaded from a replicated database. This occurs because Adaptive Server is unable to truncate the log past the LTM truncation point, even with truncate log on checkpoint set.

If you are no longer replicating this database, or are certain that you can resynchronize the replicated database, instruct the server to ignore the LTM truncation point for the database:

1> dbcc settrunc (ltm, ignore)
2> go

1> dump transaction with truncate_only

2> go

The server can now truncate the log.

1105 errors occur on tempdb while Adaptive Server is processing transactions, never during recovery.

The tempdb transaction log behaves just like a user database transaction log with the sp_configure option trunc log on chkpt set to “on”. But data management is more difficult with tempdb because it is more difficult to get information about space use: work tables cannot be referenced by the user, and the log is truncated approximately every 60 seconds. The default size of tempdb is 2MB when Adaptive Server is installed.

Possible causes of the 1105 error in tempdb, and their symptoms and solutions include:

Use the following procedure to correct 1105 errors on a user database which occur during recovery:

1. Manually set the database’s status to no chkpt on recovery and single-user:

   WARNING! Because the database was marked suspect on the original recovery attempt, this procedure also resets some internal status bits to allow the database to recover normally. Therefore, do not use this procedure under any other circumstances to avoid additional serious problems.

   1> sp_configure "allow updates", 1
   2> go
   

   1> begin transaction
   2> go
   

   1> update master..sysdatabases 
   2> set status = status & ~256
   3> where name = "<database_name>"
   4> go
   

   1> update master..sysdatabases 
   2> set status = status | 4112
   3> where name = "<database_name>" 
   4> go
   

2. Check that each of the above update commands affected only one row. If more than one row was affected, issue a rollback transaction. Otherwise, commit the transaction and shut down Adaptive Server:

   1> commit transaction
   2> go
   

   1> shutdown
   2> go
   

3. Restart Adaptive Server.

4. Dump the transaction log with the no_log option and reset the database status:

   1> use master 
   2> go
   

   1> dump tran <database_name> with no_log
   2> go
   

   1> sp_dboption <database_name>, "no chkpt", false
   2> go
   

   1> sp_dboption <database_name>, single, false
   2> go
   

   1> use <database_name> 
   2> go
   

   1> checkpoint
   2> go
   

   1> sp_configure "allow updates", 0
   2> go
   

If an 1105 error occurs on the master database during recovery and Adaptive Server does not run, correct the problem with the following procedure:

1. Create a runserver file that includes the 3607 trace flag. This trace flag allows Adaptive Server to restart without going through normal recovery, and should only be used in this procedure. Refer to “Starting Adaptive Server with trace flags” in the chapter “Other Useful Tasks” in the most recent version of Troubleshooting and Disaster Recovery for instructions.

2. Start Adaptive Server with the 3607 trace flag using the runserver file created in step 1. The recovery process performs the minimum actions required to allow access to each database, including master.

3. Once Adaptive Server has restarted and the master database is accessible, dump the transaction log with the no_log option and shut down Adaptive Server:

   1> dump tran master with no_log
   2> go
   

   1> shutdown with nowait
   2> go
   

   It is mandatory that you use shutdown with nowait here. Failure to use the with nowait option will suicide every database's transaction log.

4. Restart Adaptive Server with the original runserver file. The database should recover normally. Now dump the master database:

   1> dump database master 
   2> to <logical_dump_device> 
   3> go
   

If an 1105 error occurs on the model database during recovery, Adaptive Server may not start. The error occurs because the tempdb database is required to start Adaptive Server, but it cannot be built due to the problem with model. To correct the problem and restart Adaptive Server follow these steps:

1. Create a new runserver file that includes the 3608 trace flag. This trace flag causes Adaptive Server to recover only the master database. Refer to the section “Starting Adaptive Server with trace flags” in the chapter “Other Useful Tasks” of the most recent version of Troubleshooting and Disaster Recovery for instructions.

2. Start Adaptive Server with the 3608 trace flag using the runserver file created in step 1.

3. Manually set model status to no chkpt on recovery and single-user:

   1> sp_configure "allow updates", 1
   2> go
   

   1> begin transaction
   2> go
   
   

   1> update master..sysdatabases 
   2> set status = status & ~256
   3> where name = "model"
   4> go
   
   

   1> update master..sysdatabases 
   2> set status = status | 4112
   3> where name = "model"
   4> go
   

4. Verify that each of the above update commands affected only one row. If more than one row was affected, issue a rollback transaction. Otherwise, commit the transaction and shut down Adaptive Server:

   1> commit transaction
   2> go
   

   1> shutdown with nowait
   2> go
   

5. Restart Adaptive Server, using your regular runserver file.

6. Execute the following command to truncate the inactive portion of the transaction log in model:

   1> dump tran model with no_log
   2> go
   

7. Reset the database options on model:

   1> sp_dboption model, "no chkpt", false
   2> go
   

   1> sp_dboption model, single, false
   2> go
   

   1> use model
   2> go
   

   1> checkpoint
   2> go
   

8. Disable updates to the system catalog and shut down Adaptive Server:

   WARNING! It is mandatory that you use shutdown with nowait here. Failure to use the with nowait option will suicide every database’s transaction log.

   1> sp_configure "allow updates", 0
   2> go
   

   1> shutdown with nowait
   2> go
   

9. Restart Adaptive Server.

Versions in which this error is raised

All versions