I'm looking for a solution to the following case:
I have a trace(only captures queries above 5000ms duration, which is about 20 per hour) running in MSSQL which I can query by using:
SELECT TextData, Duration, CPU, Reads, Writes FROM fn_trace_gettable(N'C:\temp\TRACE.trc', DEFAULT) WHERE EndTime > DATEADD(MI,-15,GETDATE()) AND Reads < 10 AND Writes < 10
I want this query to run this query every 15 minutes and the results should be written(each result in it's own event log) to the eventvwr using
xp_logevent. Our monitor software will then trigger on the results of the Event Viewer.
I tried to achieve this by putting the results in a temp table, and then using a while to loop through each result. Because there are no ID's in the data, I can't keep count of the rows. Should I look at a
SELECT TOP 1 query which deletes the row after processing to the Event viewer? Or is there an easier way?
Could someone point me in the right direction? PS: This should work both on SQL 2008 R2 and SQL 2012