Is temporary table creation and dropping logged anywhere in SQL SERVER?

Question

Post reply

Is temporary table creation and dropping logged anywhere in SQL SERVER?

hurricaneDBA

SSCertifiable

Points: 5027
More actions
April 16, 2015 at 1:14 am

#295992

Dear Everyone
I have a script which generates a temporary table and then runs a script and finally drops the temporary table.
My question is does SQL SERVER 2008 R2 have any logs or features to capture the creation and dropping of this temporary table?
Please let me know
thanks
Kal

Viewing 15 posts - 1 through 15 (of 21 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

What do you mean? What are you trying to do? Please add more details.

-- Gianluca Sartori

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 2

I have a script which create a temporary table:

#TABLE1

Then a script is run which sends data to an output file and when the script completes i drop the temporary table at the end of the operation.

My question is today the output file which is generated by the script came out as having no data. So my question is; is there a way to find out if the temporary table was actually created or not?

Is there a log file in SQL SERVER which tracks the creation and deletion of temporary tables.

thanks

Kal

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

No. There's no log file showing temporary table creation and drops. Also, you can't reference a standard temporary table between sessions. You would have to create a global temporary table in order to that. With a global temporary table, you can check for its existance between sessions. But, remember that a global temporary table only lasts as long as the final session referencing it. If you have one session create the table and then close and at some later point another session is looking for the table, it won't be there.

Logically it's unclear what you're trying to do. It doesn't sound like temporary tables are the way to get whatever it is done.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 4

Just to clear the water

I am not referencing 2 sessions, i run a script daily and create/drop a local temporary table daily hence my original question was concerning if SQL SERVER captures this creation/dropping of temporary tables on a daily basis.

thank you for your reply

Kal

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

hurricaneDBA (4/18/2015)
Just to clear the water
I am not referencing 2 sessions, i run a script daily and create/drop a local temporary table daily hence my original question was concerning if SQL SERVER captures this creation/dropping of temporary tables on a daily basis.
thank you for your reply
Kal

As Grant previously stated, the answer is NO, there is no log of the temp table DDL activity that would be useful for this, in fact that alone would contradict the purpose of the minimally logged temp tables.

😎

Suggest you add your own log table and alter you script to write to that table.

hurricaneDBA SSCertifiable Points: 5027 More actions · Answer 6

hurricaneDBA

SSCertifiable

Points: 5027

April 19, 2015 at 12:24 am

#1791845

Thanks thats actually a great idea 🙂

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

hurricaneDBA (4/19/2015)
Thanks thats actually a great idea 🙂

Ah, be careful now. There's a price to be paid with Global Temp tables. Code that uses them might not be able to run concurrently.

--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)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Jeff Moden (4/19/2015)
hurricaneDBA (4/19/2015)
Thanks thats actually a great idea 🙂
Ah, be careful now. There's a price to be paid with Global Temp tables. Code that uses them might not be able to run concurrently.

My suggestion was not a global temp table but a simple audit table, i.e. one row per batch.

😎

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Eirikur Eiriksson (4/19/2015)
Jeff Moden (4/19/2015)
hurricaneDBA (4/19/2015)
Thanks thats actually a great idea 🙂
Ah, be careful now. There's a price to be paid with Global Temp tables. Code that uses them might not be able to run concurrently.
My suggestion was not a global temp table but a simple audit table, i.e. one row per batch.
😎

Understood. My response was to someone else's suggestion about possibly using GTTs. I couldn't tell if the OP was responding to you or not. Apologies for not being more clear.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

The default trace does log temp table creates and drops.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manub22 Hall of Fame Points: 3163 More actions · Answer 11

My question is today the output file which is generated by the script came out as having no data. So my question is; is there a way to find out if the temporary table was actually created or not?

What you can also do is open a SQL Profiler trace, run/start it and do the operations as mentioned in above quote.

At the end check the Profiler trace to see if your #table was created or not.

~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

GilaMonster (4/20/2015)
The default trace does log temp table creates and drops.

Awesome! Thanks, Gail!

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Jeff Moden (4/20/2015)
GilaMonster (4/20/2015)
The default trace does log temp table creates and drops.
Awesome!

Not usually. 🙂

It's one reason why the default trace sometimes covers such a short time period. Personally I'd prefer if it didn't track object creation in TempDB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

GilaMonster (4/20/2015)
Jeff Moden (4/20/2015)
GilaMonster (4/20/2015)
The default trace does log temp table creates and drops.
Awesome!
Not usually. 🙂
It's one reason why the default trace sometimes covers such a short time period. Personally I'd prefer if it didn't track object creation in TempDB.

Really? I can't find object creation in my default trace. What am I missing?

If this was not 2008, I would use Extended Events:

CREATE EVENT SESSION [track_temp_table_creation] ON SERVER

ADD EVENT sqlserver.object_created (WHERE ([database_id] = (2)))

WITH (

MAX_MEMORY = 4096 KB

,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS

,MAX_DISPATCH_LATENCY = 30 SECONDS

,MAX_EVENT_SIZE = 0 KB

,MEMORY_PARTITION_MODE = NONE

,TRACK_CAUSALITY = OFF

,STARTUP_STATE = OFF

)

GO

Maybe it works in 2008, but I don't have an instance to test.

-- Gianluca Sartori