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?

Viewing 7 posts - 16 through 21 (of 21 total)

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

spaghettidba

SSC Guru

Points: 105712

April 20, 2015 at 9:57 am

#1792133

spaghettidba (4/20/2015)

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

I just found a 2008R2 and it doesn't work. Too bad.

-- Gianluca Sartori

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 2

hurricaneDBA (4/16/2015)

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

Knowing whether the temporary tables are created or not will not will not help unless you have some indication of their content, you should set up a process audit like I suggested earlier.

😎

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

Sorry. I can help thinking about that. Auditing the content of temp tables. If someone insisted that I make that a priority at work and insisted that it be done or be fired, I'd walk out without further invitation. I just don't see the utility in doing such a thing.

This whole thread is a bit odd to me. I've been trying to convince folks over the years that using Temp Tables to Divide'n'Conquer large queries is the way to go because the large queries are going to be hammering TempDB with hash joins and work tables for index spools and accidental cross joins if you do nothing about them, especially the way some folks write them.

I'll stand by my original suggestion and that's not to worry so much about what's using TempDB. The time spent finding and ironing out poor performing and resource hungry code will return a much better ROI than searching the TempDB closet for mothy clothes, which will boil to the top of the list anyway if they are, indeed, poor performing or resource hungry moths. 😉

--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)
Intro to Tally Tables and Functions

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

spaghettidba (4/20/2015)

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?

Yep, very easy to fill up the default trace with these events and push everything else out!

Category 5, Trace Event 46 & 46. Doesn't seem to give you the name of the object though. (Object ID is there but not sure you'll get anything useful from it).

Interestingly creating indexes does at least give a name which might help.

Re: to the OP's issue - how is the data being output to a file? Could be scope issues.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

More reasons why system_health is better.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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.

Heh... suffering a bit of whiplash there. 😀 What does it take to have the default trace log the creation and drops of Temp Tables?

--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)
Intro to Tally Tables and Functions

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 7

Jeff Moden (4/20/2015)

Sorry. I can help thinking about that. Auditing the content of temp tables. If someone insisted that I make that a priority at work and insisted that it be done or be fired, I'd walk out without further invitation. I just don't see the utility in doing such a thing.

This whole thread is a bit odd to me. I've been trying to convince folks over the years that using Temp Tables to Divide'n'Conquer large queries is the way to go because the large queries are going to be hammering TempDB with hash joins and work tables for index spools and accidental cross joins if you do nothing about them, especially the way some folks write them.

I'll stand by my original suggestion and that's not to worry so much about what's using TempDB. The time spent finding and ironing out poor performing and resource hungry code will return a much better ROI than searching the TempDB closet for mothy clothes, which will boil to the top of the list anyway if they are, indeed, poor performing or resource hungry moths. 😉

My thought is that if any audit or log of data processing is required/needed, traces and sql type logs are not the right mechanisms, regardless of whether the data resides in a temporary table or not.

😎