April 8, 2010 at 5:05 am
Im running;
Windows 2003 server
MSSQL 2005 sp2
Running Perfom I intermittently see Avg. Disk Queue Length running at 100%, The HDD LED’s also flashing like mad indicating heavy disk usage. I have isolated this to MSSQL Agent, this problem only occurs when the agent is running. When the problem occurs, and I Stop MSSQL Agent the Avg. Disk queue drops. My server is using replication and a few backup jobs so my first stop was Replication. I stopped all replication tasks to no avail. I then went on to stop ALL scheduled tasks within SQL Agent, however the problem continued – stop SQL Agent and the problem stops.
What could be causing this?
Update:
I have just taken another look and there is another job that I think is causing the problem (I must have missed this when I though I had disabled all jobs)
EXEC dbo.sp_MShistory_cleanup @history_retention = 48
Is it usual for this sp to intermittently run the Avg. Disk Queue Length at 100%, If not are there any known problems with this stored procedure, I assume its needed and was created as part of the replication setup
April 8, 2010 at 7:04 am
After more investigation I’m beginning to think this isn’t a problem, and will monitor the situation.
However I have another question;
Is there a query to view the data in the distribution table, I want to check that it is retaining the 48 hours specified in this command
EXEC dbo.sp_MShistory_cleanup @history_retention = 48
I ran the following command and the result was 0
Select count(*) from MSrepl_commands (nolock)
April 8, 2010 at 3:21 pm
Disk queue lengths aren't measured or reported in percents? Busy% and Idle% are, but not queue lengths, so I can't tell what your actual queue lengths were?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply