Implementation of periodic writing timestamps in ms sql database

Question

Task:

Write timestamps into MS SQL database table every second.

Solutions:

1. External application which writes timestamps by schedule (Sql agent for example).
2. Stored procedure, which writes timestamps in infinite loop.

Questions.

1. Which of the solutions is best?
2. Is there any drawbacks of running infinite loop in stored procedure?
3. How to start stored procedure after server reboot?
4. Any other solutions?

Answer 1

1. Either, but I'd tend to the stored procedure route with WAITFOR DELAY
2. Not really
3. sp_procoption and start up stored procedures
4. Without involving external client or system, I can't think of any

Answer 2

1, Both have advantages and disadvantages. Evaluate and choose based on your environment

Procedure Advantages:
- Don’t have overhead of SQL Agent processing once per second. (In fact, I don't think you can get SQL Agent to consistantly launch the same job once per second.)
- I don’t think a procedure in WAITFOR mode is using resources – but you’d want to check

Procedure Disads:
- If procedure fails (gets stopped somehow), it won’t start up. (Unless you have a SQL Agent job running to check if the procedure has stopped, in which case might as well go with the procedure)
- Might be easier to stop/break than you’d think (concurrency/deadlocks, detached DBs, manually stop during maintenance then forget to restart)

Job Advantages:
- If job fails (perhaps db is unavailable), next job will still get launched

Job Disads:
- Seems very kludgy, having SQL agent run every second. Measure required server overhead if you do this.
- If SQL Agent fails or is stopped, job won’t run

A suggestion: must it be once per second? Can it be once per 5, 10, 15, or 30?

2, Shouldn’t be any, barring what’s mentioned above. Make darn sure you can’t hit locking, blocking, or deadlock situations!

3, Like @gbn says, sp_procoption

4, Nothing that doesn’t involve cumbersome tricks based on pessimistic locking techniques, or byzantine logic based on the timestamp (not datetime) datatype. The best fix would seem to be the long-term one of combining those two databases into one, but that’s not a short-term option.

Out of sheer paranoia, I'd combine the two like so:

• Job set to run every 2, 3, or 5 minutes
• Job calls procedure that updates your timesampt, then waitfors a few seconds
• Procedure does not stop, so job continues to run; while job is running, it will not be started (because it's still executing)
• If procedure somehow dies, job will launch it again next time it's scheduled to run.

Answer 3

Try using SQL Service broker to do this asynchronously and its queue system allows you to not miss any data even if the SQL Server service would have to be restarted. I ve used this once sometime back for this kind of polling scenario.

http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx#sqlsvcbr_topic2

This might help, http://msdn.microsoft.com/en-us/library/bb839488.aspx