Recently one user reported he is facing some issue while deleting the table records. When I checked it there was a blocking. Immediately I tried to kill the session which was causing the issue. I used KILL spid to kill the session. Though it was executed successfully, session was still running with 'suspended' status by holding a blocking on sessions.
In order to kill the session, I alter the database to Single User mode.
USE MASTER
GO
ALTER DATABASE (DBNAME) SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE (DBNAME) SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
This resolved the issue by killing all the sessions. But it was on a NonProduction database. When doing it on Production database please be aware of affected users and the work to be redone.
No comments:
Post a Comment