Search code examples
sql-servercollate

Database collation error in SQL Server Management Studio

I'm trying to change the collation of my database in SQL Server Management Studio but I'm always getting an error

Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

I set first the database into Single User Mode by using the below query:

ALTER DATABASE [DatabaseName] 
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

And then I followed the below query:

ALTER DATABASE [DatabaseName] 
    COLLATE SQL_Latin1_General_CP1_CI_AS

and then I got the error I mentioned above.

I know this kind of problem is already posted in here, but I tried the solution I saw but nothing works. Hope you can help me.

Thanks!

Solution

  • Lawrencxe.Try this please:

    1. Execute SP: SP_LOCK

    2.In Results you will get SPID, DBID, OBJID, INDID, TYPE, RESOURCE, MODE, STATUS

    3.Then check the status column, if it is showing WAIT then kill that SPID. For example,to kill a particular SPID 76 Execute SP:

      `Kill 76` (Where 76 is SPID)