SQLTeam.com | Weblogs | Forums

How to delete the session_id from sp_who2 in SQL server 2008


#1

Hi,

I tried to delete the backup created by automated scheduler backup from one of the folder but the file not deleted due to another program running. When I checked that I found that the in sp_who2, a session_id was suspended against this database. I tried to kill/rollback that session_id but the session_id not deleted.
Can anyone help me to delete that session_id in sp_who2 so that the backup file will be deleted from the folder & we create the another backup using the different backup scheduler.

Please help


#2

Hi.
@pankaj2910
1.Try to Kill session ID By the command:kill (You should be sa account login)
OR
2.Try to Change the Owner of the Database which database is performing the backup.
OR
3.if not able to change the owner then disable the owner.kill the Id & again Enable The owner(be Careful while disabling the owner as it can impact other database also)

Regards
Suman


#3
  1. A query gets suspended when it is requesting access to a resource that is currently not available. This can be a logical resource like a locked row or a physical resource like a memory data page. The query starts running again, once the resource becomes available.

  2. High disk IO means that a lot of data pages need to be accessed to fulfill the request.
    That is all that I can tell from the above screenshot. However, if I were to speculate, you probably have an IO subsystem that is too slow to keep up with the demand.

This could be caused by missing indexes or an actually too slow disk. Keep in mind, that 15000 reads for a single OLTP query is slightly high but not uncommon.