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

I got the above error in ASP.NET 3.5, Visual Studio 2008. I had simply updated a stored procedure that was part of another database. The only change that I made was add an additional paramater to be passed to the stored procedure. Well I begin to get this error without any good reason. I researched and found a couple of helpful command to check database integerity like these


DBCC CHECKDB

but that did not help much as I could not figure out what the different results means. So then I run my own query to find out which tables were last updated in the database using this query


SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseTable, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'MYDBNAME')

Surprisingly it ran successfully but gave 0 number of rows. I had run this query before and I knew it gave me the list of tables that were last modified. So I ran it on the actual live server and indeed I got the result.

Turns out my db has been corrupted. I have a test system and I can probably restore a fresh copy into my system, that will solve my problem. Just wanted to put this out. The above query can easily verify the integrity of your database, rather than running some complex and intensive commands that may stress your database as well. At least in my case, it did.

Advertisements
This entry was posted in Software Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s