SQL 6.5 Logsegment full, but it isn't

[moonshadow]

We've a production database running on SQL 6.5 (ugh!), every night we take a dump, copy it to the backup server and restore it onto 2 databases there. One we use as a warm standby and apply 6 minute transaction logs during the day. The other is used for adhoc queries. We have a batch job that runs immediately after the restore to add some more users to the query database. This is failing with error:
Msg 1105, Level 17, State 2, Server FIFE, Procedure sp_adduser, Line 154
Can't allocate space for object 'Syslogs' in database 'copypor01' because the 'logsegment' 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.

On the face of it, it looks like a simple space issue, but I've recently increased the database size by 2GB. Stopping and starting SQL Server clears the problem. However, SQL enterprise manager says there is no space left in the database, but 1GB left in the transaction log. This must be inaccurate, as the production system reports the same but is running fine. How can the transaction log be full after a full restore ? Or what else could cause this ?
Can anyone help?

 

[moonshadow]

The same thing happened again this morning. Has anyone got any suggestions at all?

 

[moonshadow]

Ok, ran DBCC CHECKALLOC on the query database after the failure today. It reported error 2558 Extent not within segment: Object %ld, indid %d includes extents on allocation page %ld which is not in segment %d
Looking in BOL for this message, it implies that the sysusages data for the dumped database and the target for the restore must match exactly - otherwise we end up in this situation with data on log pages and vice versa. I am going to attempt to drop and recreate the target database, restore the dump and see if DBCC CHECKALLOC then runs OK. If not, BOL suggests scripting the original database (thank goodness for SQL 2000 QA which can do this), and creating the target with this script. All seems a bit weird to me. Can anyone clarify?

 

[evatype2]

You need to have the databases mapped across your devices exactly the same if you want to restore off a dump. Dumps are not data exports, they map to the physical device layout. Haven't worked with the product for a while, but you are getting error messages that go back to 3.x and 4.x. Look at your syssegments table. Database A must be laid out like databse b for a physical restore to work correctly. Chances are space was added to one and not the other.

 

[moonshadow]

Deleted the database and the devices, recreated them all, and all seems ok now. I think evatype2's post was on the correct lines.