Making sure that SQL Agent is running

On several occasions, the SQL Service has been shut down
and restart. In these cases, the SQL Agent does not auto
re-start, and overnight processing has not been completed.
Is there:
a) A way to make sure that the SQL Agent ALWAYS starts
when the SQL Service is started. I know I can do this if
the server is re-booted, but if you just stop and re-start
the SQL Service, the SQL Agent doesn't re-start.
b) From a stored procedure, or perhaps VB, inquire on the
running status of the SQL Agent (we have a monitor tool
that could be expended to report if the agent isn't
running) How does the SQL Enterprise Manager determin if
the SQL Agent is running ?
Thanks,
Joe.You can write a proc which uses xp_cmdshell and executed NET START and then park the proc as an auto
start proc (sp_procoption).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Joe Farr" <joefarr@.hotmail.com> wrote in message news:091801c36e0d$179a1530$a401280a@.phx.gbl...
> On several occasions, the SQL Service has been shut down
> and restart. In these cases, the SQL Agent does not auto
> re-start, and overnight processing has not been completed.
> Is there:
> a) A way to make sure that the SQL Agent ALWAYS starts
> when the SQL Service is started. I know I can do this if
> the server is re-booted, but if you just stop and re-start
> the SQL Service, the SQL Agent doesn't re-start.
> b) From a stored procedure, or perhaps VB, inquire on the
> running status of the SQL Agent (we have a monitor tool
> that could be expended to report if the agent isn't
> running) How does the SQL Enterprise Manager determin if
> the SQL Agent is running ?
> Thanks,
> Joe.
>|||That will do fine - thanks Tibor.
>--Original Message--
>You can write a proc which uses xp_cmdshell and executed
NET START and then park the proc as an auto
>start proc (sp_procoption).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Joe Farr" <joefarr@.hotmail.com> wrote in message
news:091801c36e0d$179a1530$a401280a@.phx.gbl...
>> On several occasions, the SQL Service has been shut down
>> and restart. In these cases, the SQL Agent does not auto
>> re-start, and overnight processing has not been
completed.
>> Is there:
>> a) A way to make sure that the SQL Agent ALWAYS starts
>> when the SQL Service is started. I know I can do this if
>> the server is re-booted, but if you just stop and re-
start
>> the SQL Service, the SQL Agent doesn't re-start.
>> b) From a stored procedure, or perhaps VB, inquire on
the
>> running status of the SQL Agent (we have a monitor tool
>> that could be expended to report if the agent isn't
>> running) How does the SQL Enterprise Manager determin if
>> the SQL Agent is running ?
>> Thanks,
>> Joe.
>
>.
>|||For a robust production-grade setup, you may want to
configure your monitoring tool, which should be
independent of SQL Server, to restart the SQLAgent
service. Most enterprise-class monitoring tools such as
NetIQ and Patrol suppor such a configuration.
Linchi
>--Original Message--
>On several occasions, the SQL Service has been shut down
>and restart. In these cases, the SQL Agent does not auto
>re-start, and overnight processing has not been completed.
>Is there:
>a) A way to make sure that the SQL Agent ALWAYS starts
>when the SQL Service is started. I know I can do this if
>the server is re-booted, but if you just stop and re-
start
>the SQL Service, the SQL Agent doesn't re-start.
>b) From a stored procedure, or perhaps VB, inquire on the
>running status of the SQL Agent (we have a monitor tool
>that could be expended to report if the agent isn't
>running) How does the SQL Enterprise Manager determin if
>the SQL Agent is running ?
>Thanks,
>Joe.
>.
>

Make SQL Server Agent job stop after X minutes

I swear there is a way in SQL 2000 to have a SQL Server Agent job (that runs
a DTS) to stop running if it's been running for X minutes. I can't find it
though. I thought it was just a checkbox and a field for a number somewhere.
any ideas?
Rich
sp_trace_setstatus @.traceid = @.TraceID, @.status = 0
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C99F8FAF-A4AE-40B5-B12E-B380E258A127@.microsoft.com...
>I swear there is a way in SQL 2000 to have a SQL Server Agent job (that
>runs
> a DTS) to stop running if it's been running for X minutes. I can't find
> it
> though. I thought it was just a checkbox and a field for a number
> somewhere.
> any ideas?

Make SQL Server Agent job stop after X minutes

I swear there is a way in SQL 2000 to have a SQL Server Agent job (that runs
a DTS) to stop running if it's been running for X minutes. I can't find it
though. I thought it was just a checkbox and a field for a number somewhere.
any ideas?Rich
sp_trace_setstatus @.traceid = @.TraceID, @.status = 0
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C99F8FAF-A4AE-40B5-B12E-B380E258A127@.microsoft.com...
>I swear there is a way in SQL 2000 to have a SQL Server Agent job (that
>runs
> a DTS) to stop running if it's been running for X minutes. I can't find
> it
> though. I thought it was just a checkbox and a field for a number
> somewhere.
> any ideas?

Make SQL Agent use drive mapping?

Good day,
I'm migrating our current SQL 2k box (running on win 2000) to a new Windows 2003 server. I'm having trouble getting the SQL agent to use the servers drive mapping when running jobs. The odbc driver that I'm using does not support UNC paths. I know, dri
ve mapping are not cool becuase we have to keep the server logged in but it's the only way and it does work on our old box. The weird thing is that I can't get this working on the new server. On the old box the SQL Agent is set to run as the same accoun
t as the logged in user and it can access the drive mappings fine, it's been running this way for quite some time. However, on my new box this does not work...is this a windows 2003 server thing? I'm runing SQL 2k SP3 on both boxes. Also, the new box i
n not in a domain so the account I'm trying to use is a local admin acount.
Any ideas?
Drive mappings are not guaranteed to work when being accessed from a
service. There is no way to force them to work.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I figured it out. I simply added a step in each scheduled job to create and then delete the drive mapping.
For example, I just use the "net use f: \\server\volume" to create the drive ampping an then excute the rest of my job steps and then use another step with this command "net use f: /delete" to delete it. Becuase the SQL Agent service is excuting the comm
and the drive mapping is in it's session and it works every time. It's nice becuase the drive mapping isn't even seen by the desktop and works when the server in not logged in. I remove the drive mapping at the end of each job becuase the drive mapping
remains in tack as long as the SQL agent service stays running and therefore if another job runs and tries to re-create the drive mapping the job will fail.

Make SQL Agent use drive mapping?

Good day,
I'm migrating our current SQL 2k box (running on win 2000) to a new Windows
2003 server. I'm having trouble getting the SQL agent to use the servers dr
ive mapping when running jobs. The odbc driver that I'm using does not supp
ort UNC paths. I know, dri
ve mapping are not cool becuase we have to keep the server logged in but it'
s the only way and it does work on our old box. The weird thing is that I c
an't get this working on the new server. On the old box the SQL Agent is se
t to run as the same accoun
t as the logged in user and it can access the drive mappings fine, it's been
running this way for quite some time. However, on my new box this does not
work...is this a windows 2003 server thing? I'm runing SQL 2k SP3 on both
boxes. Also, the new box i
n not in a domain so the account I'm trying to use is a local admin acount.
Any ideas?Drive mappings are not guaranteed to work when being accessed from a
service. There is no way to force them to work.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I figured it out. I simply added a step in each scheduled job to create and
then delete the drive mapping.
For example, I just use the "net use f: \\server\volume" to create the drive
ampping an then excute the rest of my job steps and then use another step w
ith this command "net use f: /delete" to delete it. Becuase the SQL Agent s
ervice is excuting the comm
and the drive mapping is in it's session and it works every time. It's nice
becuase the drive mapping isn't even seen by the desktop and works when the
server in not logged in. I remove the drive mapping at the end of each job
becuase the drive mapping
remains in tack as long as the SQL agent service stays running and therefore
if another job runs and tries to re-create the drive mapping the job will f
ail.

Maintenance Wizard error...

Hi,

I'm just trying to use the Maintenance Wizard for the first time, but the SQL Server Management Studio shows me an error message about 'Agent XPs' is not running on my server; and I should activate it through 'sp_configure'?

I've tried to understand 'sp_configure' by searching it on MS TechNet, etc. But of course, because I'm not a SQL Server expert user; I don't even know how to use 'sp_configure' at all....

Anyone know how to work this thing around? Appreciate all the help...

PS: I almost forgot to mention, that I'm using MS Windows Small Business Server R2 2003 with SQL Server 2005 Workgroup Edition.

sp_configure is used to view/change global settings for the current server. To enable them, connect to Management Studio, start a new query, and view the current configuration. For more options with sp_configure, check out:

http://msdn2.microsoft.com/en-us/library/ms188787.aspx

To enable Agent XP's, you can try something like this:

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
go
RECONFIGURE
GO

Thanks,
Sam Lester (MSFT)

|||

Sam,

Thanks for your reply there...

But unfortunately, I am a very novice at SQL Server, is there a step-by-step documentation on how to start using sp_configure?

Thank you again.

|||

The link I supplied explains the entire syntax for sp_configure, one of many system stored procedures used in managing your server. I'd suggest taking a look at the tutorials included in books online (the documentation portion of SQL Server 2005). Here is a good one on getting familiar with Management Studio, including writing T-SQL statements (such as sp_configure):

http://msdn2.microsoft.com/en-us/library/ms167593.aspx

Thanks,
Sam

|||

Hello -

I'm not an expert on SQL Server Management Studio, but I just ran into the same issue and here's how I got around it:

I loaded up SQL Server Management Studio|||

Chris,

Is it really true, you did have the similar problem with Maintenance Wizard like I do? Then perhaps it's like I'm most affraid of, Microsoft actually de-activate it by default settings in SQL Server 2005 Workgroup Edition... The question is why?

I did try to look up in the Object Explorer in SQL Server Management Studio first before post a thread in here, because I didn't find anything that said about Agent XPs. But maybe it's just that missed it, I'll take a look it again when I got back to my office then (too bad right now I'm on vacation, gives me a jeepers to leave the server's database un-protected like that ).

Thanks for the feedback Chris! Hopefully others who has the same problem with Maintenance Wizard will post too, so Microsoft could give a good explanation why there's such issue in the first place.

Maintenance Plans, SQL Agent daily restart needed

We have a SQL 2000 server running on Server 2003, both updated, that has a
persistent problem running Maintenance plans, and thus a database backup.
Trying to troubleshoot, I’ve updated, deleted and recreated the maint plans,
restarted the server altogether, SQL itself and the SQL Agent; the long and
short of it is that if I restart the SQL Agent daily, the maintenance plan
will run.
Previously, this server has been running the maintenance plans without fail
for literally years, and now this problem has cropped up over the last couple
of months. No significant changes have been made to the server (other apps,
etc).
Having to restart SQL agent daily doesn't make any sense - any ideas?
Gary
Waht version (sp and hotfix)?
Select @.@.version
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:8AB0A498-B268-4797-BD3E-8EDBD58B131A@.microsoft.com...
> We have a SQL 2000 server running on Server 2003, both updated, that has a
> persistent problem running Maintenance plans, and thus a database backup.
> Trying to troubleshoot, I've updated, deleted and recreated the maint
> plans,
> restarted the server altogether, SQL itself and the SQL Agent; the long
> and
> short of it is that if I restart the SQL Agent daily, the maintenance plan
> will run.
> Previously, this server has been running the maintenance plans without
> fail
> for literally years, and now this problem has cropped up over the last
> couple
> of months. No significant changes have been made to the server (other
> apps,
> etc).
> Having to restart SQL agent daily doesn't make any sense - any ideas?
> --
> Gary

Maintenance Plans, SQL Agent daily restart needed

We have a SQL 2000 server running on Server 2003, both updated, that has a
persistent problem running Maintenance plans, and thus a database backup.
Trying to troubleshoot, Iâ've updated, deleted and recreated the maint plans,
restarted the server altogether, SQL itself and the SQL Agent; the long and
short of it is that if I restart the SQL Agent daily, the maintenance plan
will run.
Previously, this server has been running the maintenance plans without fail
for literally years, and now this problem has cropped up over the last couple
of months. No significant changes have been made to the server (other apps,
etc).
Having to restart SQL agent daily doesn't make any sense - any ideas?
--
GaryWaht version (sp and hotfix)?
Select @.@.version
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:8AB0A498-B268-4797-BD3E-8EDBD58B131A@.microsoft.com...
> We have a SQL 2000 server running on Server 2003, both updated, that has a
> persistent problem running Maintenance plans, and thus a database backup.
> Trying to troubleshoot, I've updated, deleted and recreated the maint
> plans,
> restarted the server altogether, SQL itself and the SQL Agent; the long
> and
> short of it is that if I restart the SQL Agent daily, the maintenance plan
> will run.
> Previously, this server has been running the maintenance plans without
> fail
> for literally years, and now this problem has cropped up over the last
> couple
> of months. No significant changes have been made to the server (other
> apps,
> etc).
> Having to restart SQL agent daily doesn't make any sense - any ideas?
> --
> Gary

Maintenance plans

I am not sure if this is the correct forum, but here goes.
can anyone tell my what account (SQL Agent or SQL Server) runs Maintenance
plans? I always thought it was the Agent that ran all jobs on the server. I
am using 2 different Accounts, one for server and the other for Agent. the
server keeps trying to log onto the server where my backups are going not
Agent. Is this the way it's supposed to be?
Thanks,
Joe
Agent executes an xp which in turn executes an exe file which logs on to SQL Server and execute the
BACKUP command and then SQL Server tries to access the drive where to put the backup. So, it is SQL
Server that need permission.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46733E11-AF11-4398-AC8F-4F6FE694A54D@.microsoft.com...
>I am not sure if this is the correct forum, but here goes.
> can anyone tell my what account (SQL Agent or SQL Server) runs Maintenance
> plans? I always thought it was the Agent that ran all jobs on the server. I
> am using 2 different Accounts, one for server and the other for Agent. the
> server keeps trying to log onto the server where my backups are going not
> Agent. Is this the way it's supposed to be?
> Thanks,
> Joe
|||Thank you!
"Tibor Karaszi" wrote:

> Agent executes an xp which in turn executes an exe file which logs on to SQL Server and execute the
> BACKUP command and then SQL Server tries to access the drive where to put the backup. So, it is SQL
> Server that need permission.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:46733E11-AF11-4398-AC8F-4F6FE694A54D@.microsoft.com...
>