February 2, 2009 at 1:22 pm
I wondered if anyone else has seen contention in tempdb due to the SQL Agent? I've got an issue where contention seems to increase over time (about 24 to 30 hours), to the point where the SQL server almost grinds to a halt. At that point, restarting the SQL Agent fixes the issue and the server runs normally again. Currently managing the issue through a proactive restart of the agent every 12 hours.
The issue isn't load-related - under heavier load, it's fine so long as the agent is restarted regularly.
It's SQL 2005 (build 3050) 64-bit, Enterprise Edition, with 6 mirrored databases.
February 2, 2009 at 1:29 pm
Have you checked a trace on tempdb to see what's building up crud in there?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 1:42 pm
Can't see the wood for the trees sometimes. Thanks, I'll try that. If restarting the agent fixes the issue, then would you assume that whatever's causing the problem is being run by the agent?
February 2, 2009 at 2:02 pm
The Agent comes in both x86 and X64 so you may be running the 32bits version instead of the 64bits version. And mosts things you do with the Agent Microsoft in 2005 wants Admin permissions which could be Proxy accounts.
Kind regards,
Gift Peddie
February 2, 2009 at 2:37 pm
If any job the agent is running hits tempdb, and doesn't close its connection or some such, then yeah, it could cause a problem.
If, for example, you have some ETL process being run that uses tempdb to sort and aggregate a worktable, and it's hanging on an uncommitted transaction or some such, then I would definitely expect it to eventually show up as a problem in tempdb. Restarting the agent would close the connection, which would allow tempdb to drop those resources, just as it does with anything of that sort.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply