Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update stats process not getting killed Expand / Collapse
Author
Message
Posted Tuesday, February 14, 2012 11:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 21, 2013 8:43 PM
Points: 148, Visits: 566
Windows server 2003
SQL Server 2000 enterprise edition. 32 bit.

Hi,
I have set up a maintenance plan to update stats. Unfortunately I picked 100% sampling to update the stats. Last weekend, the job to run the maintenance plan got kicked off and I started to get blocking issues because of this. The update stats command was blocking other queries.

So I first stopped the job but the SPID that was running update stats kept running. I tried to kill the SPID and it kept on running. I verified that it was in a roll back state. But as soon as it rolled back update stats on one table, it started to update stats on the next table.

So in spite of stopping the job and killing SPID several times the process to update stats kept running and finally after 35 hrs it finished with an error.
I could have restarted the instance to get rid of it but didn't want to take a risk as it was no longer causing blocking.

Has anyone else seen this behaviour or knows how to kill a SPID?


Blog
http://saveadba.blogspot.com/
Post #1252072
Posted Wednesday, October 10, 2012 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:09 AM
Points: 15, Visits: 521
For SQL 2008, here is what we do:

ALTER DATABASE [XXX] SET AUTO_UPDATE_STATISTICS OFF WITH ROLLBACK IMMEDIATE
ALTER DATABASE [XX] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH ROLLBACK IMMEDIATE


select Q.job_id,Q.database_id,Q.in_progress from sys.dm_exec_background_job_queue Q
where Q.in_progress = 1 AND Q.database_id = 9
There is then a function called KILL STATS JOB Y

Post #1370901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse