SQL Server Performance Forum – Threads Archive
Can’t allocate space for object
Msg 1105, Level 17, State 1, Server Myprodserver, Procedure VCB_Edi_867_Translate_test, Line 41Can’t allocate space for object ‘edi_867_archive’ in database
‘SundryUSAT’ because the ‘default’ segment is full. If you ran out of
space in Syslogs, dump the transaction log. Otherwise, use ALTER
DATABASE or sp_extendsegment to increase the size of the segment
This is SQL 6.5. anyone have any idea, why this issue and how to solve it? please advice
Add another database device and expand the database using that device from Enterprise manager. 6.5 BOL refers
Use one of the following procedures to correct the error, depending on whether the error occurred during run time or recovery.
Run-time 1105 errors
The specific action you take on a run-time 1105 error depends on the object ID.
Object ID ¹ 8 In this case, the message indicates that the data segment is full on the database indicated in the message. To obtain more data space, do one or more of the following:
·Use alter database to increase the size of the data segment.
·Drop objects from the database.
·Delete rows from tables in the database. Object ID = 8 In this case, the message indicates that the log segment is full on the database indicated in the message. 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. For information about managing the transaction log, see Chapter 4, "Transaction Log Management."
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. Note The database dump is not required if your environment does not save transaction logs for media failure recovery. Do not assume that the occurence of error 1105 means that your transaction log is too small. If the data and the log are on the same segment, the actions described above can often free enough space without requiring you to increase the size of the transaction log.
If you are concerned that your transaction log is too small, read Chapter 4, "Transaction Log Management," before increasing the transaction log size. For information about using the alter database statement to increase log size, see the Microsoft SQL Server Transact-SQL Reference. Recovery 1105 errors
The specific action you take on a recovery 1105 error depends on which type of database it occurs on.
On a user database If error 1105 occurs on a user database during recovery, correct the problem using the following procedure:
1.Use the sp_dboption system procedure to note the current user options on the database (so you can reset them in step 7).
2.Manually set the database status to no chkpt on recovery (status bit 16) and single user (status bit 4096) by adding the two status bits together and then using the | (OR) operator, as follows:
sp_configure ‘allow updates’, 1
go
reconfigure with override
go
begin tran
go
update master..sysdatabases
set status = status | 4112
where name = ‘database_name’
go Caution 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. Do not use this procedure under any other circumstances. The value of 4112 in the SET STATUS statement corresponds to the single user and no chkpt on recovery database options.
3.Check that the SET STATUS statement affected only one row.
4.If more than one row was affected, issue a rollback transaction statement. Otherwise, commit the transaction and shut down SQL Server:
commit tran
go
shutdown
go
5.Restart SQL Server.
6.After you restart SQL Server, dump the transaction log with the no_log option and reset the database status:
dump tran database_name with no_log
go
sp_dboption database_name, ‘no chkpt’, false
go
sp_dboption database_name, single, false
go
use database_name
go
checkpoint
go
sp_configure ‘allow updates’, 0
go
reconfigure with override
go
7.Use sp_dboption to reestablish any database options such as select into/bulkcopy noted in step 1. On the master database If error 1105 occurs on the master database during recovery but SQL Server still starts, correct the problem by logging in and dumping the transaction log using the NO_LOG option, as shown in the following example:
dump tran master with no_log If error 1105 occurs on the master database and prevents SQL Server from starting, contact your primary support provider.
On the model database If error 1105 occurs on the model database during recovery, SQL Server will not start. This is because the tempdb database, which is required to start the server, could not be built due to the problem with the model database. To correct this problem and restart SQL Server, use the following procedure:
1.Start SQL Server with the 3608 trace flag. This trace flag causes SQL Server to recover only the master database at startup. For details about using trace flags, see "Using Trace Flags," in Chapter 24, "Additional Problem-solving Techniques."
2.Execute the following statements to set the no chkpt on recovery option on the model database:
sp_dboption model, ‘no chkpt’, true
go
use model
go
checkpoint
go
3.Restart SQL Server.
4.Execute the following statement to truncate the inactive portion of the transaction log in model:
dump tran model with no_log
go
5.Reset the database option on model:
sp_dboption model, ‘no chkpt’, false
go
use model
go
checkpoint
go
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>