0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lawrencxe
  • 81
  • 4
  • 18
  • Which processes are running on your server? Have a look with right click on the database in SSMS then go to the activity monitor. Maybe you are even blocking yourself with an open SSMS window. My suggestion would be to be sure no processes (also no own processes) are running that could interfere and then to run the query again. – casenonsensitive Jul 29 '20 at 15:41
  • How would I know if I blocking myself? Thanks – Lawrencxe Jul 30 '20 at 12:02
  • Everytime you use `use [DatabaseName]` and keep that session/SSMS tab open, you might be blocking yourself. The easiest way to prevent that is to close all open SSMS tabs/windows and reopen just one for the alter database command. – casenonsensitive Jul 30 '20 at 12:07

1 Answers1

1

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)
Olga Romantsova
  • 1,096
  • 1
  • 6
  • 8
  • Hi Olga, I execute ```SP_LOCK``` and then the status is GRANT and I don't see SPID 76 – Lawrencxe Jul 30 '20 at 08:48
  • Hi, Lawrencxe. Please,check the column "status". Find where status=wait. And then kill that SPID ( kill number SPID) – Olga Romantsova Jul 30 '20 at 09:56
  • Hi Olga, all the status when I execute ```SP_LOCK``` is Grant. – Lawrencxe Jul 30 '20 at 11:56
  • Lawrencxe, is there another error appear? or there's still Transaction (Process ID 56) was deadlocked on lock resources with another proces...? – Olga Romantsova Jul 30 '20 at 13:02
  • You can run the following statement to return a list of collations that are supported by your version of SQL Server: SELECT name, description FROM sys.fn_helpcollations(); – Olga Romantsova Jul 30 '20 at 13:09
  • Hi, I killed the open connections but now I having a new error – Lawrencxe Jul 30 '20 at 14:09
  • ```The object ' ' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.``` – Lawrencxe Jul 30 '20 at 14:09