Help needed - can't override table locks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help needed - can't override table locks

Author

Topic

shadowcra
Starting Member

2 Posts

Posted - 2009-01-16 : 07:44:45

Hi,

we have the following table:

CREATE TABLE [dbo].[UsersFriendships] (
[UserID1] int NOT NULL,
[UserID2] int NOT NULL,
[Confirmation1] tinyint CONSTRAINT [DF__UsersRela__Confi__2A4B4B5E] DEFAULT 0 NOT NULL,
[Confirmation2] tinyint CONSTRAINT [DF__UsersRela__Confi__2B3F6F97] DEFAULT 0 NOT NULL
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idxUsersRelationshipsUserIDs] ON [dbo].[UsersFriendships]
([UserID1], [UserID2])
ON [PRIMARY]
GO

It currently hold about 6,000,000 records. Our problem is it doesn't use the index for queries.
We have confirmed that changing the index to a unique index would solve our problem. However, we cannot alter the index as even if we are the only users on the database (the website that access it is taken offline), we still get a LOCK notification.
We have also attempted creating a second table with the right index and moving the data there. For some reason, we cannot INSERT a single record without getting the same LOCK error. Your help would be much appreciated. I'll be happy to provide any further needed information to solve this issue.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 08:04:13

The fastest way is to drop and recreate the index when offline.

E 12°55'05.63"
N 56°04'39.26"

shadowcra
Starting Member

2 Posts

Posted - 2009-01-16 : 21:33:22

quote:
Originally posted by Peso

The fastest way is to drop and recreate the index when offline.

E 12°55'05.63"
N 56°04'39.26"

Thanks for the quick reply. What I ended up doing was copying the content of the table to a duplicate table with a proper index using the SQL Server Management Studio. That seemed to have worked without locks, and now that the table is properly indexed it can easily be accessed again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources