[dogboy11]

Trying to expand a production database and I get the following error:

Attempt to insert duplicate key row in object 'sysusages' with unique index
'sysusages'

Anyone know what the issue is on this? Thanks.


DB

[trvishi]

Originally Posted by dogboy11

Trying to expand a production database and I get the following error:

Attempt to insert duplicate key row in object 'sysusages' with unique index
'sysusages'

Anyone know what the issue is on this? Thanks.


DB

Did you try a control-c when you were doing an alter database? This is a bit serious if you did as you may end up having a sysusages entry which is not valid.

[trvishi]

Also, post the command which you are trying. Guessing its alter database blah.

Most probably your problem is the memory structure of the database conflicts with the sysusages table. And usually this happens when you did a control-c in the middle of an alter database.
You can try this if thats the case. But dont assume its a simple operation.

First, a select * from sysusages for the dbid of the user database. Then, execute dbcc dbtable for the database. This requires sybase_ts_role to be granted and set:


1>use master

2>go

1>sp_role "grant", "sybase_ts_role", sa

2>go

1>set role "sybase_ts_role" on

2>go

1>dbcc traceon(3604)

2>go

1>dbcc dbtable(dbname)

2>go

1>dbcc traceoff(3604)

2>go



Compare the number of rows in the sysusages table in the master database for this dbid with the number of dm_segmap entries in the output from dbcc dbtable for the database. They should be equal. If they are not equal, then execute the following command:



1>dbcc dbrepair(<dbname>, remap)

2>go



This command is used to update the dbtable structure sysusages information by reading the current sysusages information for the database name.



It is a good idea to put the user database in single-user mode prior to running the dbcc dbrepair. After the dbcc dbrepair is run, take the single-user option off the database and the database should be able to be altered successfully.

[trvishi]

Also, DO NOT SHUT DOWN the SERVER until you fix this.

[dogboy11]

I figured the problem out .... I don't remember ever breaking out of an "alter database" command .... I try not to do that .... that aside I did do a sp_dbremap dbname and that seemed to fix it. Thanks for your help.

DB