Can't allocate space

[Lionel Falk]

Hi,
I've got a table with 65'000 records and when I do a SELECT * FROM tablename ORDER BY Name I receive this error message:

Msg 1105, Level 17, State 1
Can't allocate space for object '-513' 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 or sp_extendsegment to increase the size of the segment.
Msg 1510, Level 17, State 2
Sort failed: Out of space or locks in database 'tempdb'

So I've dumped the transaction with no_log and also I've extended the segement from the master database (because tempdb is in it bydefault):

sp_extendsegment system, master

But I've still got the error message. Is there anybody who can advice me?
Thank you

[Chris]

Try deleting your backup device used for trans dumps (MAKE SURE YOU RECORD THE NAME AND LOCATION BEFORE DELETING)...once you've deleted it from SQL Ent Manager, delete the file from your drive (using windows explorer). Then recreate the device in SQL Ent Manager and re-run the trans dump. It should work.


------------
Lionel Falk at 8/6/99 2:31:39 PM

Hi,
I've got a table with 65'000 records and when I do a SELECT * FROM tablename ORDER BY Name I receive this error message:

Msg 1105, Level 17, State 1
Can't allocate space for object '-513' 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 or sp_extendsegment to increase the size of the segment.
Msg 1510, Level 17, State 2
Sort failed: Out of space or locks in database 'tempdb'

So I've dumped the transaction with no_log and also I've extended the segement from the master database (because tempdb is in it bydefault):

sp_extendsegment system, master

But I've still got the error message. Is there anybody who can advice me?
Thank you

[Ray Miao]

The size of tempdb is 2mb by default, it's too small for most production systems. You can expand it to other device. The command for expanding database is 'alter database', refer to BOL for syntax.


------------
Chris at 8/6/99 3:08:08 PM

Try deleting your backup device used for trans dumps (MAKE SURE YOU RECORD THE NAME AND LOCATION BEFORE DELETING)...once you've deleted it from SQL Ent Manager, delete the file from your drive (using windows explorer). Then recreate the device in SQL Ent Manager and re-run the trans dump. It should work.


------------
Lionel Falk at 8/6/99 2:31:39 PM

Hi,
I've got a table with 65'000 records and when I do a SELECT * FROM tablename ORDER BY Name I receive this error message:

Msg 1105, Level 17, State 1
Can't allocate space for object '-513' 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 or sp_extendsegment to increase the size of the segment.
Msg 1510, Level 17, State 2
Sort failed: Out of space or locks in database 'tempdb'

So I've dumped the transaction with no_log and also I've extended the segement from the master database (because tempdb is in it bydefault):

sp_extendsegment system, master

But I've still got the error message. Is there anybody who can advice me?
Thank you