Post reply

System Process Causing Blocking

  • November 7, 2013 at 12:12 pm

    #284856

    Hi, I have a SQL Server 2008 R2 box that has a system process that is blocking my user processes.

    Using sp_who2, the Command is TASK MANAGER and is running against my production database:

    SPID STATUS LOGIN

    36 BACKGROUND sa

    COMMAND

    TASK MANAGER

    Has anybody seen this before?

    Running dbcc inputbuffer(36) gives me the following results:

    EventTypeParametersEventInfo

    No Event 0 NULL

    ...which makes this even more perplexing.

  • November 7, 2013 at 2:37 pm

    #1664932

    Rather than sp_who2 and DBCC INPUTBUFFER, I would use a better tool to discover locking and blocking.

    Adam Machanic wrote a masterpiece called sp_whoisactive, that you can find here: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/30/twenty-nine-days-of-activity-monitoring-a-month-of-activity-monitoring-part-30-of-30.aspx

    I could tell you to query sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_requests... but Adam did it much better than you and I could ever do, so why not just reuse his outstanding work?

    sp_whoiasctive can show you the whole locking chain and tell you which resource sessions are waiting on. Give it a go.

    -- Gianluca Sartori

  • November 7, 2013 at 3:06 pm

    #1664939

    Thanks! I actually used sp_whoisactive after I wrote my post. The result was that the system process waiting on wait type PAGEIOLATCH_SH for like three hours. It didn't give me the SQL that was running, that field was blank.

    I did, however, get rid of the blocking. I ran DBCC DROPCLEANBUFFERS. I fully understand the ramifications of doing that, but it was really the lesser of two evils.

    Thanks again!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply