Discussion Locked

SQLMaint job fails with error in SQL 6.5

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have the following job scheduled in my sql server 6.5 managed taks area:

SQLMAINT.EXE -D trecs -CkDBNoIdx -CkAlNoIdx -Rpt e:\MSSQL\LOG\trecs_maint.rpt

It always fails with this message repeated several times (only with different allocation pages values) in the log file:

Extent not within segment: Object 240003886, indid 0 includes extents on allocation page 1985024 which is not in segment 1.

The SQL Server log has this in it several times as well:

extent 1985032 is in the wrongsegment

How do I resolve these errors?

3 replies

by spydergst1

WOW! This is serious. Ok here it goes.

Lets say you have a database 20GB (original) the you increase the database size by 10 (seg2)now it is 30Gb. Then you increase it by 15Gb (seg3). Now its a 45Gb database.

So you dump the 45Gb database. Then you try to restore form the dump. Well, the 45Gb database is seen as 20+10+15 (seperate segments of the same database so to speak).

Some data in the restore is looking for original segment or some data is looking for seg1 or seg2 but, it can't find it because the dump is not a segmented database it's seen as just a 45Gb database without 20 or 10 or 15. I use space segment as a means to describe the problem as the are no real segments in a datbase.

So how do you resolve this? Well, you have to recreate the database in the same order the original database was created. Create a 20GB DB then increase it by 10Gb then increase it 15Gb. The dump will restore fine. Or, you can create a database bcp all the data out and bcp it into the new database. Don't forget to script out all your tables first.

If you don't know the history of the database as in, when the size was increased you can pull this information out of the master DB.

I hope this is not a production DB!! Good luck

by lschneider

I ended up using BCP to dump the affected objects(tables) and then refreshed them from the BCP'd file back into the database.

Thanks

This discussion is closed to new replies.