July 17, 2008 at 10:38 am
Hi,
In one of the distribution box (transactional replication) sql server service is terminating at a particular time. We had monitored using the profiler the application that was executing was SQL Agent - Enabling/disabling Agent XPs. After executing the below it is getting stopped. At the particular time nobody is logging into the server either by TS or connecting to SQL Server. SQL Server is 2005 Ent Ed with SP2
declare @agent_enabled bit declare @show_advanced bit select @show_advanced = cast(value_in_use as bit) from sys.configurations where name = N'show advanced options' select
@agent_enabled = cast(value_in_use as bit) from sys.configurations where name = N'Agent XPs' if 0 <> @agent_enabled begin if 1 <> @show_advanced begin exec sys.sp_configure
@configname = N'show advanced options', @configvalue = 1 reconfigure with override end exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 0 reconfigure with
override if 1 <> @show_advanced begin exec sys.sp_configure @configname = N'show advanced options', @configvalue = 0 reconfigure with override end end
Any idea how to resolve this. We are suspecting that one of the replication jobs is doing this. Any help to find resolve or debug the issue will be greatly appreciated.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 17, 2008 at 11:00 am
Are you sure the service is stopping and not just databases set on auto-disconnect, or something like that?
And, if the service is stopping, do you have it set to auto-restart on failure?
- 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
July 17, 2008 at 11:22 am
Yes the service is getting stopped. And the autorestart option is enabled. But even though the server is not getting auto started once it is stopped. We have to manually start the services.
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 17, 2008 at 11:53 am
Is there anything else running on the server, or just Windows Server and SQL Server?
- 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
July 17, 2008 at 11:59 am
Just windows and sql server 2005 ent ed with sp2
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 17, 2008 at 12:03 pm
chandramohann (7/17/2008)
Yes the service is getting stopped. And the autorestart option is enabled. But even though the server is not getting auto started once it is stopped. We have to manually start the services.
This would mean the instance is stopped by a stop service request !
-Check out your sqlagent jobs that may contain a shutdown statement
- Check out if there is a scheduled task (windows) that might shut id down
net stop mssql$yourinstance
Also monitor logins at windows level !
Maybe sqlservers errorlog file has more info for you.
Did you check the windows eventlog ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply