20 Temp table tracking

Question

Post reply

20 Temp table tracking

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
January 25, 2022 at 9:52 pm

#3977165

I have temp table created by application which starts from SST, SSW, DDL and this gets deleted after the process is completed.
Note: for example every temp table is unique and starts like for SST --> like SST_1234, SST_12345, SST_2345 etc.
Usually the temp table process completes in 30 sec and gets deleted.
I need to setup a alert when this temp table are more than 50 at same given time
I also need to setup an alert when particular temp table takes more than 1 min.
Nita

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 135274 More actions · Answer 1

you could implement a database ddl trigger handling on object creation for tables starting with those prefixes

( handle registration of create table + timestamp and drop table (+ timestamp if you want to keep the logged rows ) )

and activate a job-schedule which executes every 1 minute that runs a job checking the interval time and raises the alert

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

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 2

Anyone please help with the code

I have temp table created by application which starts from SST, SSW, DDL and this gets deleted after the process is completed.

Note: for example every temp table is unique and starts like for SST --> like SST_1234, SST_12345, SST_2345 etc.

Usually the temp table process completes in 30 sec and gets deleted.

I need to setup a alert when this temp table are more than 50 at same given time

I also need to setup an alert when particular temp table takes more than 1 min.

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 3

You're going to have to write some code that checks sys.tables for names using wild cards and check their "create_date" for the date and time they were created. If that proc finds something "out of spec", then you need to set it up to email the right people that can do something about it and then schedule that job to run once per minute. I don't believe there's a way to set up an actual "Alert" for such a thing but I could be wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)