October 21, 2008 at 2:18 am
hi
i want to run my JOB continuously (24*7)
can anybody help me on this ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 21, 2008 at 2:33 am
bhuvnesh.dogra (10/21/2008)
hii want to run my JOB continuously (24*7)
can anybody help me on this ?
Running a job continuously leads to performance issues.
But if still you want you can use an infinite while loop in the job.
or the best alternative would be
set the job execution time on the System idle state, then the job will run as it will find the system idle
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 21, 2008 at 2:37 am
What's your JOB going to do ?
Does it poll for something to do, and if there is, do it.
Or
Does it produce continuos load on the SQL Server for load testing 😉
If it polls for something to do,
- you might think about reacting on the changes via a trigger
so it would only be fired, when a change occures
or
- you might start your JOB on a regular basis of
frequency "daily"
and daily frequency "every 1 minute"
which certainly is more like "producing continuos load"
devloping robust and performant databaseapplications with Microsoft SQL-Server
October 21, 2008 at 2:44 am
hi
actually following are the steps i need to do.
1.On every alter or create Stored proc ,A DDL trigger gets fired
2.DDL trigger insert SP name into SYNC_OBJECT_SEND_IN
3.JOB continuosly check above table and migrate ita data on remote server through linked server.
Code executed in JOB
----------------------------------------------------------------
declare @Tmp table (
[object_text] [varchar](max) Not null,
[object_name] [varchar](200) Not null,
[eventtype] [varchar](100) Not null,
[ServerName] [varchar](15) Not null,
[Created_By] datetime Not null
);
DELETE FROM dbo.SYNC_OBJECT_SEND_IN OUTPUT DELETED.* INTO @Tmp
INSERT INTO [srvr2\dev].DBname.dbo.SYNC_OBJECT_RECEIVE(object_text,object_name,eventtype,ServerName,created_by)
SELECT * FROM @Tmp
--------------------------------------------------------------------------
So eventually on every DDL trigger firing data should be migrate to srvr2
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 21, 2008 at 2:47 am
how to set JOB ,to work it when system sits in IDLE state
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 21, 2008 at 2:55 am
in window "new job schedule"
select schedule type = "start whenever the cpu becomes idle"
devloping robust and performant databaseapplications with Microsoft SQL-Server
October 21, 2008 at 6:03 am
You should look up "Service Broker" in books online.
October 22, 2008 at 5:58 am
I'm intrested in the same thing but to run an extended stress/load test. Service Broker doesn't seem to fit the bill. I have a package set up that runs a query which takes about 20 minutes to complete. I'm trying to do two different things:
- Create multiple threads for a stress test
- Continously loop the task for a load test.
Does anyone have a suggestion other than just a package with a for loop?
October 22, 2008 at 6:08 am
Your two requirements:
- Create multiple threads for a stress test
- Continously loop the task for a load test.
These would also tend to direct me toward Service Broker. A queue can be configured to process in parallel with any number of threads and it will automatically process all incoming messages continuously.
To multi-thread something that will continuously check a table for new records is going to be complicated in MS SQL. The job agent will not allow you to run a single job in parallel. So to use the job agent, you would probably have to dynamically add jobs.
For internal queue monitoring, MS SQL has Notification Services and Service Broker. Notification Services has been removed as a feature, so you should not use it. That leaves you with Service Broker. Your only other realistic, scalable solution would be to create a service (in any programming language of your choice) that monitors your table and spawns new threads to process records as they come in. However, this is essentially what Service Broker does, so building something yourself seems a bit pointless.
Service Broker is a bit complicated. Since there is no UI, the DBA's that have not spent much time really managing SQL Servers strictly with T-SQL can be quickly overwhelmed. I would recommend you find and read a book about Service Broker.
October 22, 2008 at 6:39 am
Indeed, service broker is a bit hard to monitor and to figure out what's going on.
You really need to play with it, to get the grips.
Here's an overview of my adventures :
http://www.sqlservercentral.com/articles/Service+Broker/2897/
and a little aid towards monitoring:
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/
Regarding the OP.
According to the books, one should first deploy ddl in a dev environment and restrict it in production :hehe:
You could also schedule your job to run every single minute.
It is asynchronously anyway.
- Do you really need it spot on ??
- What if it doesn't work at the other side ?
- what if the other side is down ?
One of my peers pointed me to this fact:
If you need it really really bad, that's probably the way you're gone get it.
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
October 22, 2008 at 7:51 am
At my blog (in my signature, below) you will find my slide set and code examples of traps to avoid when setting up service Broker.
[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]
October 22, 2008 at 11:19 am
Check out
http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm. The article is for 2000, wouldn't be surprised if it also applies to 2005.
HTH,
MJ
October 22, 2008 at 1:08 pm
rbarryyoung (10/22/2008)
At my blog (in my signature, below) you will find my slide set and code examples of traps to avoid when setting up service Broker.
Very nice presentation indeed :w00t:
Hey .... you even mentioned my article and scripts :hehe:
Thanks.
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
October 22, 2008 at 1:14 pm
Thanks, Johan. 🙂
[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]
October 22, 2008 at 3:24 pm
ALZDBA (10/22/2008)
rbarryyoung (10/22/2008)
At my blog (in my signature, below) you will find my slide set and code examples of traps to avoid when setting up service Broker.Very nice presentation indeed :w00t:
Hey .... you even mentioned my article and scripts :hehe:
Thanks.
The "live" version was even more entertaining!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply