Indexes and not enough space in tempdb

[Yongle Guo]

This database is running on SQL sever 6.5.
We are running an home-created stored procedure that includes several rounds of dropping and recreating indexes in a few tables, i.e., after dropping indexes, the tables are truncated, then a lot of records will be inserted into the table, and the indexes are recreated.
we are getting the following error in turns:
1. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server]Can't
allocate space for object '-841' in database 'tempdb' because the
'system' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE...

We then used EM to expand the tempdb, when reran the procedure, we got the following message:

2. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server] Cannot
drop the index 'dbo.ItemBalamce_CCB_Id',because it doesn't exist in the
system catalogs.
However, when I check the mentioned index, it is certainly there.
How could this happen?
After several rounds of running theis procedure, these two error messages appeared to occur alternatively.
If you have an answer or suggestion, please let me know ASAP.
Many thanks.

[David Lee]

Have you recompiled the stored procedure after increasing the size?
Is it possible to rewrite the stored procedure to include a loop to delete the log on checkpoint? Setting @@rowcount to a certain number so only a certain number of rows will be effected at one time and deleting the log before it gets full. I've used this method before and it is a good way to get around the "full log" problem.

------------
Yongle Guo at 7/20/01 5:13:34 PM

This database is running on SQL sever 6.5.
We are running an home-created stored procedure that includes several rounds of dropping and recreating indexes in a few tables, i.e., after dropping indexes, the tables are truncated, then a lot of records will be inserted into the table, and the indexes are recreated.
we are getting the following error in turns:
1. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server]Can't
allocate space for object '-841' in database 'tempdb' because the
'system' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE...

We then used EM to expand the tempdb, when reran the procedure, we got the following message:

2. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server] Cannot
drop the index 'dbo.ItemBalamce_CCB_Id',because it doesn't exist in the
system catalogs.
However, when I check the mentioned index, it is certainly there.
How could this happen?
After several rounds of running theis procedure, these two error messages appeared to occur alternatively.
If you have an answer or suggestion, please let me know ASAP.
Many thanks.

[Dilip Vyas]

Dear Yongle Guo

first of all you truncate the log of tempdb database, so that all uncommited
transaction will be wiped out. and after that increase the size of tempdb ( by adding a device to tempdb ) to avoid this type of situation.

Regards
Dilip Vyas
SQL Server DBA
Reliance Petroleum Limited
Jamnagar , Gujarat, India


------------
Yongle Guo at 7/20/01 5:13:34 PM

This database is running on SQL sever 6.5.
We are running an home-created stored procedure that includes several rounds of dropping and recreating indexes in a few tables, i.e., after dropping indexes, the tables are truncated, then a lot of records will be inserted into the table, and the indexes are recreated.
we are getting the following error in turns:
1. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server]Can't
allocate space for object '-841' in database 'tempdb' because the
'system' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE...

We then used EM to expand the tempdb, when reran the procedure, we got the following message:

2. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server] Cannot
drop the index 'dbo.ItemBalamce_CCB_Id',because it doesn't exist in the
system catalogs.
However, when I check the mentioned index, it is certainly there.
How could this happen?
After several rounds of running theis procedure, these two error messages appeared to occur alternatively.
If you have an answer or suggestion, please let me know ASAP.
Many thanks.