BACKGROUND SPID causing blocking.

hey guys,

 

can throw light on issue. it's not easy 1 explain i’ll give go.

 

we have spid appearing on our server, sp_who2 returns;

 

spid 

66

 

status

background

 

login

sa

 

hostname

.

 

blkby

.

 

command

insert

 

cputime

280313

 

when execute dbcc inputbuffer returns 'no events'

 

when @ locks held spid holds locks in database , tempdb [bulk insert]. i've looked @ sqlhandle because appear internal returns nothing.

 

this spid causing blocking locks takes out.

 

to remove spid have found if disable service broker spid removed. might red herring have switch database single user mode using rollback immediate.

 

the reason why think service broker involved because spid returns when service broker re-enabled.

 

we thought might because of full text search uses materialised view dropped views , issue still persists.

 

i know vague description please feel free ask questions, on appreciated

 

thanks

 

phil harbour

 

/* --------------------- begin database information --------------------- */

-- declare cursor variables

declare @db_info_string nvarchar(4000)

, @name varchar(256)

, @database_id char(5)

, @compatibility_level char(5)

, @recovery_model_desc nvarchar(120)

, @collation_name varchar(256)

, @state_desc nvarchar(120)

, @dfs varchar(20)

, @lfs varchar(20)

, @plu varchar(20)

, @logwarning varchar(30)

, @is_auto_create_stats_on char(1)

, @is_auto_update_stats_on char(1)

, @is_auto_update_stats_async_on char(1)

, @is_parameterization_forced char(1)

-- insert column headers file

select @db_info_string = char(13) + 'database information' + char(13) + 'database name, database id, compatibility level, recovery model description, collation name, state description, data file(s) size (kb), log file(s) size (kb), percent log used, log warning, is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_on, is_parameterization_forced'

exec @rc = sp_oamethod @fileid, 'writeline', null, @db_info_string

-- raiseerror if line write fails

if @rc <> 0

begin

print 'error: writing string data file'

end

-- begin cursor

declare db_info_cursor cursor forward_only

for

select d.name

, d.database_id

, d.compatibility_level

, d.recovery_model_desc

, d.collation_name

, d.state_desc

, s.[data file(s) size (kb)]

, s.[log file(s) size (kb)]

, s.[percent log used]

, case when s.[percent log used] > 75 then 'more 75% of log used' else '' end logwarning

, d.is_auto_create_stats_on

, d.is_auto_update_stats_on

, d.is_auto_update_stats_async_on

, d.is_parameterization_forced

from sys.databases d

join (

select *

from (

select instance_name as database_name

, counter_name

, cntr_value

from sys.dm_os_performance_counters

where object_name like '%atabases%'

and counter_name in ('data file(s) size (kb)', 'log file(s) size (kb)', 'percent log used')

and instance_name != '_total'

) p

pivot (min(cntr_value) for counter_name in ([data file(s) size (kb)], [log file(s) size (kb)], [percent log used])

) as q) as s on d.name = s.database_name

open db_info_cursor

fetch next from db_info_cursor into

@name, @database_id, @compatibility_level, @recovery_model_desc, @collation_name, @state_desc

, @dfs, @lfs, @plu , @logwarning , @is_auto_create_stats_on, @is_auto_update_stats_on, @is_auto_update_stats_async_on

, @is_parameterization_forced

-- loop through cursor record set

while @@fetch_status = 0

begin

select @db_info_string = @name + ',' + @database_id + ',' + @compatibility_level + ',' + @recovery_model_desc + ',' + @collation_name + ',' + @state_desc

+ ',' + @dfs + ',' + @lfs + ',' + @plu + ',' + @logwarning + ',' + @is_auto_create_stats_on + ',' + @is_auto_update_stats_on + ',' + @is_auto_update_stats_async_on

+ ',' + @is_parameterization_forced

exec @rc = sp_oamethod @fileid, 'writeline', null, @db_info_string

-- raiseerror if line write fails

if @rc <> 0

begin

print 'error: writing string data file'

end

fetch next from db_info_cursor into

@name, @database_id, @compatibility_level, @recovery_model_desc, @collation_name, @state_desc

, @dfs, @lfs, @plu , @logwarning, @is_auto_create_stats_on, @is_auto_update_stats_on, @is_auto_update_stats_async_on

, @is_parameterization_forced

end

-- close , deallocate db cursor

close db_info_cursor

deallocate db_info_cursor

 

/* --------------------- end database information --------------------- */

 

SQL Server  >  SQL Server Database Engine