Clean up agent

is there a way to tell the status of a clean up agent?
Nothing is in the history for him , but when I right click on him my
option is "stop agent".. start agent is grey'd out which tells me he is
running. I do a sp_who2 on the dist. and don't see anything about him
there.
What else can i look for.
Thanks,
-comb
What about if you run sp_help_job and look at 'current_execution_status'?
Rgds,
Paul Ibison
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.replication:63692
In article <OsgLtBJXFHA.2420@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> What about if you run sp_help_job and look at 'current_execution_status'?
> Rgds,
> Paul Ibison
>
>
yea i just did that and it does tell me that much.
thanks though paul. I appreciate your help as always.
-comb
sqlsql

Classification has been delayed because the routing information...

Any ideas on why I get this message in the transmission status:

Classification has been delayed because the routing information is currently being updated.

I did create/drop some routes because I had pointed them to the wrong service, but that was some time ago.

I also just tried to restart the sql instance and it hung on stopping so I'm assuming something with the routing caused the issue.

Any ideas?

Thanks.

This error is cause by lock timeouts on reading the routing table (sys.routes) in the database where you're sending from and/or lock timeouts in the tempdb (where information about all SSB services in the SQL instance is stored). If you're hitting this, it means there must be some transaction holding a long lock on a route or on tempdb.

I would recommend using sys.dm_tran_locks, sys.syslockinfo or sp_lock to identify the lock that is blocking the SSB router from classifying messages.

|||

Thanks. I figured it out later on that I had inadventantly started a transaction to send a message and didn't commit it and after that I changed some of the routes so this held everything up.

Thanks.

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?
Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checkpoint in master database after startup

When we stop and start our server we see a checkpoint process on master that
is in suspend status. The date on the process is the date and time the
server was started. Is this a normal process?Randy,
Yes, it is perfectly normal. For me it is usually between spids 15 and 17.
If you run sp_who2 (spidnumber) on it occasionally, you will see the CPU and
IO increments over time as checkpoint operations run.
I believe you are on 2005, because on 2000 it usually shows as 'sleeping'
and CHECKPOINT SLEEP.
RLF
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:18A38741-B8F4-4DCA-9EA7-39882857738B@.microsoft.com...
> When we stop and start our server we see a checkpoint process on master
> that
> is in suspend status. The date on the process is the date and time the
> server was started. Is this a normal process?

Checking the status of replicated transaction

I have an ETL process setup in my OLAP stagging area for a particular
reporting application. The stagging area consists of articles from several
publishers. I have a two step external process that runs, the first step
updates the status of detail items in each publication, the second step
synchronizes my transformation area from the changes in the replicated
transactional tables. My problem is how do I know that the distributor has
for each publication has posted all of the transaction to their respective
subscribers so when I run my synchronize transformation process I get ALL of
the updated data. This sounds like pretty common functionality, I'm just not
sure how to implement... Please help...?
Dan B
Dan,
you could have a 'master' job. This job has several steps - a step to run
each distribution/merge agent and a final step to run the transformation
process.
HTH,
Paul Ibison
|||Hi Paul,
Thanks for your reply... I'm implementing the concept of a master job
already, I don't know how to run the distribution agent remotely in T-SQL, I
was thinking that this agent is already running...? Maybe I'm getting
confused... I thought that you could setup the distribution agent to
immediately update subscribers or queue the updates, I think that ours is
configured to immediately update so I guess I just need to know if there are
any pending transactions on the publisher... Does that make any sense...?
Dan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23wdeDX$YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Dan,
> you could have a 'master' job. This job has several steps - a step to run
> each distribution/merge agent and a final step to run the transformation
> process.
> HTH,
> Paul Ibison
>
|||Dan,
to run the transformation task after all synchronizations have finished
you'll need to schedule the synchronizations on specific times rather than
continuously. To run the distribution agents, which is essentially a series
of jobs, you can run sp_start_job.
HTH,
Paul Ibison

checking the status of merge agent

My merge agent is getting stopped at midnight. because of which i have large
number of records which needs to be merged when i come in the morning.
Is it possible To create a job which will run every 3-4 hours, which will
check whether the merge agent is running or not? If the merge agent is
stopped then start the merge agent.
Is there a better way? Any suggestions?
just reschedule your merge agent to run every 5 minutes, or have step 4 on
failure return to step 1.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> My merge agent is getting stopped at midnight. because of which i have
large
> number of records which needs to be merged when i come in the morning.
> Is it possible To create a job which will run every 3-4 hours, which will
> check whether the merge agent is running or not? If the merge agent is
> stopped then start the merge agent.
> Is there a better way? Any suggestions?
>
|||> just reschedule your merge agent to run every 5 minutes,
This is a cool option...

> or have step 4 on failure return to step 1.
There is no step 4. I am using continous merge replication. There is only
one step. Hence i can't go to the first step. Or am I understanding it in a
different way?
"Hilary Cotter" wrote:

> just reschedule your merge agent to run every 5 minutes, or have step 4 on
> failure return to step 1.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> large
>
>
|||right click on your agent in the merge agents folder, select agent
properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
around to step 1 on failure. Click on the advanced tab to do this.
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> This is a cool option...
> There is no step 4. I am using continous merge replication. There is only
> one step. Hence i can't go to the first step. Or am I understanding it in
a[vbcol=seagreen]
> different way?
>
> "Hilary Cotter" wrote:
on[vbcol=seagreen]
will[vbcol=seagreen]
|||I know what you are saying.
But in my agent property i have only one step. No 3 or 4 steps.
I am using merge replication. What does the 2nd and 3rd step contain?
"Hilary Cotter" wrote:

> right click on your agent in the merge agents folder, select agent
> properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
> around to step 1 on failure. Click on the advanced tab to do this.
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> a
> on
> will
>
>
|||is this an ActiveX script you are running? Or are you pulling from SQL CE?
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...[vbcol=seagreen]
> I know what you are saying.
> But in my agent property i have only one step. No 3 or 4 steps.
> I am using merge replication. What does the 2nd and 3rd step contain?
> "Hilary Cotter" wrote:
wrap[vbcol=seagreen]
only[vbcol=seagreen]
in[vbcol=seagreen]
step 4[vbcol=seagreen]
have[vbcol=seagreen]
morning.[vbcol=seagreen]
which[vbcol=seagreen]
agent is[vbcol=seagreen]
|||I am not using ActiveX script
Also not SQL CE
"Hilary Cotter" wrote:

> is this an ActiveX script you are running? Or are you pulling from SQL CE?
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...
> wrap
> only
> in
> step 4
> have
> morning.
> which
> agent is
>
>

checking sql agent status

Is there any possible way to use T-sql checking sql agent status
thanx for your repliesHi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

checking sql agent status

Is there any possible way to use T-sql checking sql agent status?
thanx for your repliesHi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

checking sql agent status

Is there any possible way to use T-sql checking sql agent status?
thanx for your replies
Hi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

Checking Merge agent status in SQL2005

Hi sql gurus,
Is there any way to check the status of the merge agent job per subscriber
in SQL 2005?
Below is the same check for SQL 2000:
use distribution
select runstatus from MSmerge_history as h
where h.timestamp in (select max(timestamp) from MSmerge_history where
agent_id =
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'<subscriber_server_name>')) )
I discovered the solution
use distribution
select mh.*, isnull(ms.runstatus,0) as runstatus
from dbo.MSmerge_history mh with (READPAST), dbo.MSmerge_sessions ms with
(READPAST)
where mh.timestamp in (select max(timestamp) from MSmerge_history where
agent_id in
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'subscriber_server_name'))) and ms.agent_id = mh.agent_id

Checking Jobs Status through Query

I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.

Thanks in Advance

Jacx

you can use opennrowset or openquery to

query other servers

job information are stored in msdb and you can invoke the

following to query job information

use msdb
select * from sysjobs
select * from sysjobhistory

|||

tually i wanted a code to find the urrent job status of a particular job which i am inerested in. Kindly help me with that.

Thanks

Jacx

|||

use msdb
select * from sysjobs sj join --<change the * to get only the columns you need
sysjobhistory sjh
on sj.job_id=sjh.job_id
where name like 'W%' <modify this for the job name

check the run status

check this link

http://msdn2.microsoft.com/en-gb/library/ms174997.aspx

Checking Job Status using T-SQL

Hi.

How can i check the job status using SQL Query?

The following query may help you...

Select
*
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
Name='Your Schd. Package Name'
Order By
Run_Date Desc,
run_time Desc

|||Look up using [sp_help_job] in Books Online.|||

by the way....

What is meant by JOB STATUS ?

|||

Raja, I meant If the job (last execution/historical) executed successfully or not..From the above query..

KangKang, use RUN_Status column to find the status

1=Executed Successfuly

0=Failed with error

When Failed you can find the details from the Message Column

|||

I get too many recrod when i use this method

Select
DISTINCT run_status
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
name = 'STR Balance'

I just want to get the current run_stauts and store it in a variable. I have refer to SP_Help_Job but i do not know how can i return the run_Status

|||

actually i just want to return the current status, i tried but it return the new and old execute status.

|||I tryto change the SP_HELP_JOB but its too complicated, Is there any other method so i can insert all things into temp table and select current_status from the temp table.|||

I guess I don't understand why you think sp_help_job is so complicated.

You give it a job name, and it gives you a status. Actually, very simple.

|||

YA actually is very easy.....I should try more before post...:).

|||

The problem with using sp_help_job in a batch is that you can't return the results to a table. This is because sp_help_job itself returns results to a table as part of its processing, and

An INSERT EXEC statement cannot be nested.

|||

I have modify the sp_help_job and the sp_get_composite_job_info to retrun only 1 value.

:)

Checking job status using SQL-DMO

I have a question I hope someone can help me with.

My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.

The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.

My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).

Is anyone able to shed some light on this?

I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed

Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?

Thanks for any help,
AndrewDoesn't anyone have any ideas?

Should I be posting this elsewhere instead?|||Will VB/VBA (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ex02_1gv7.asp) do?

-PatP

check the status of SQL Agent

Hi all

in my project, I need to access SQL job to finish something. But sometimes, the status of SQL Agent is not running, which needs me to check the status first. I am wonder are there some functions or some ways to check the status. If you know, please response me.

I appreciate your response !

You don't need to check for status if you create a proxy account for the Agent, that is clone an Admin level permissions to run the Agent, run a search for SQL Server Agent Proxy account in the BOL(books online). The reason is when you run a Job manually it runs in the context of your permission as a Job it becomes the Agent's which means it needs your level of permissions to run it. Hope this helps.|||

Hi Caddre

Thanks for your response. I project workd is like this:

it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?

Thank you very much !

|||

Liu_andi:

Hi Caddre

Thanks for your response. I project workd is like this:

it need to restar the machine in which SQL settled. After it checked the Sql Connection is re-established.(means the machine is restarteed), then it call some jobs. but an exception was thrown out, say,"Cannot execute ...., Sql Agent is running". so I make my thread sleeping for about 10 seconds. the exeception is not thrown out. but after I translated the project in other sql groups machines. the exception is out again. so I think I should check the status of the SQL Agent. do you have some suggestion?

Thank you very much !

When you start SQL Server you are starting the SQL Server service it is different from the Agent without the proxy account the Agent will not be running in an application as you expect it, threads are not relevant to the issue it is related to the Agent having enough permission to run, it takes a lot of people time to understand but fix it or your jobs will not run as you expect. Hope this helps.

|||

Hi Caddre

you are so nice to give me response.

you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:

exec master..xpcmdshell 'net start'.

the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?

|||

Other way I think is more better to use:

declare @.service nvarchar(100)

select @.service = case when charindex('\',@.@.servername)>0
thenN'SQLAgent$'+@.@.servicename
else N'SQLSERVERAGENT' end

exec master..xp_servicecontrol N'QUERYSTATE', @.service

the return is "Running.", which expresses the status of SqlAge is running.

|||

Liu_andi:

Hi Caddre

you are so nice to give me response.

you mentioned a proxy account in the response, I forgot to tell you that I use a domain account which have admin permission, to access SQL. I found a way to check whether the Service runs or not, by following sql querying sentence:

exec master..xpcmdshell 'net start'.

the query will return a table in which includes all the service which status is running. we can check whether SQL Agent is running or not by finding the key word"SQLSERVERAGENT" in the table. How about your idea?

This is a query against one database that exposes all your SQL Server that is not prudent, what I am telling you to do is a simple process running under a service clean and valid. I am telling you to do it the correct way what you actually do is your employer's business. Hope this helps.

check the status of SQL Agent

Hi all

In my project, i need to call some jobs to finish some things. But sometimes, Sql Agent is not run. I need to check the status of it. Are there some functions or other ways to check it. if you know, please response me.

thank you !

You can use this script

declare @.service nvarchar(100)

select @.service = case when charindex('\',@.@.servername)>0
then N'SQLAgent$'+@.@.servicename
else N'SQLSERVERAGENT' end

exec master..xp_servicecontrol N'QUERYSTATE', @.service|||Great answer, thank you !