Sql Server Agent Job

Question

Post reply

Sql Server Agent Job

DKlein

SSCrazy

Points: 2351
More actions
August 19, 2008 at 9:43 am

#130768

Hello all,
I am trying to set up a Sql Server Agent Job that archives certain data from a trace table that is truncated every night. I step up a Transact-SQL script step in which I run the following script:
Begin Transaction
INSERT INTO [Database].[dbo].[TRACE_Archive]
([TextData]
,[LoginName]
,[SPID]
,[Duration]
,[StartTime]
,[EndTime]
,[Reads]
,[Writes]
,[CPU])
SELECT *
FROM Database.dbo.trace
WHEREtextdata like '%UpdateAccount%'
Commit Transaction
GO
The problem is that the job runs everyday without incident in the Job History log, but the data is not inserted into the TRACE_Archive table. Any suggestions?
Thanks,
DK

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Are you specifying the correct value for the TextData column in the WHERE clause?

Greg

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

please post the result of...

SELECT count(*)

FROM Database.dbo.trace

WHERE textdata like '%UpdateAccount%'

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

DKlein SSCrazy Points: 2351 More actions · Answer 3

There are definitely results each day because I end up running the above-mentioned script manually without the Begin and Commit transaction. The query I posted at the onset returns about 120 rows by end-of-day.

The current results of the above listed count(*) query are:

80

The Where clause is searching for the name of a Stored procedure, the results in the Trace_Archive for the TextData have been trapping only that stored procedure to this point without exception.

Thanks for the responses,

DK

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

Double check the time when the job runs. Is it possible it's running after the table has been truncated?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

DKlein SSCrazy Points: 2351 More actions · Answer 5

“Double check the time when the job runs. Is it possible it's running after the table has been truncated?”

I scheduled my job to run at 5:00pm, EST. The stored procedure that runs the trace kicks-off each morning at 9:00am and runs till 5:00pm, one of it’s first steps is to truncate the Trace table. I have been running the manual version of the script somewhere around 5:15pm to 5:30pm, after I check the Trace_Archive table, to see if the job failed to insert.

Any suggestions, should I try to execute a stored procedure made up of the above listed script?

Thanks,

DK

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

I'm just grasping at straws here but try scheduling the job to run around 5:15.

Just a wild guess but I'm wondering if for some reason the data is not available yet. I'm asking myself what's different between the manual run and the scheduled query. The time is different, hmmmm ....... and so is the user?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]