#table OWNER

Question

#table OWNER

tutupouet

SSCommitted

Points: 1512
More actions
December 9, 2011 at 7:59 am

#249399

Hello,
is there a solution to know the spid (or other) who own a # table in tempdb.
Thx

Viewing 14 posts - 1 through 13 (of 13 total)

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

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

Not directly.

You can see how much tempDB space is used by each connection via sys.dm_db_session_space_usage, but you can't like a temp table name back to a session_id.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

chauchs (12/9/2011)
Hello,
is there a solution to know the spid (or other) who own a # table in tempdb.
Thx

Why do you want to know that anyways?

You can't remote drop that table.

You'd need to kill the connection to do that. But I wouldn't do that without knowing the consequences to your application.

tutupouet SSCommitted Points: 1512 More actions · Answer 3

Arfff !!! :doze:

Thx, but this is not the answer that I was hoping

Does the value after the '_' represents something (in the name in tempdb.sys.objects)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

chauchs (12/9/2011)
Arfff !!! :doze:
Thx, but this is not the answer that I was hoping
Does the value after the '_' represents something (in the name in tempdb.sys.objects)

The _s and the hexadecimal after that is made only to uniquify the table name.

What are you trying to do, what problem are you trying to solve?

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

chauchs (12/9/2011)
Arfff !!! :doze:
Thx, but this is not the answer that I was hoping

Sorry, but it's how it is

Does the value after the '_' represents something (in the name in tempdb.sys.objects)

A random hexadecimal hash.

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

tutupouet SSCommitted Points: 1512 More actions · Answer 6

I want to shrink tempdb but I have a message

DBCC SHRINKFILE: Page 1:378832 could not be moved because it is a work table page.

.I cant restart the instance.

I tried DBCC FREESYSTEMCACHE ('ALL') but allways the message

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

chauchs (12/9/2011)
I want to shrink tempdb but I have a message
DBCC SHRINKFILE: Page 1:378832 could not be moved because it is a work table page.
.I cant restart the instance.
I tried DBCC FREESYSTEMCACHE ('ALL') but allways the message

Not the way to do this.

What does this return?

SELECT log_reuse_wait_desc FROM sys.databases where name = 'tempdb'

If it returns active transaction then you need to find that transaction and see how long 'till it finishes or what are the impact of killing it.

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

You are, I assume, aware that shrinking TempDB without completely and totally quiescing the server can cause corruption that will require a restart to fix?

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

GilaMonster (12/9/2011)
You are, I assume, aware that shrinking TempDB without completely and totally quiescing the server can cause corruption that will require a restart to fix?

Wow, I had missed that memo. Good to know.

tutupouet SSCommitted Points: 1512 More actions · Answer 10

tutupouet

SSCommitted

Points: 1512

December 12, 2011 at 3:07 am

#1419908

ok ... I planned a restart

thx for all

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

Ninja's_RGR'us (12/9/2011)
GilaMonster (12/9/2011)
You are, I assume, aware that shrinking TempDB without completely and totally quiescing the server can cause corruption that will require a restart to fix?
Wow, I had missed that memo. Good to know.

http://support.microsoft.com/kb/307487

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

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

chauchs (12/9/2011)
I want to shrink tempdb but I have a message
DBCC SHRINKFILE: Page 1:378832 could not be moved because it is a work table page.
.I cant restart the instance.
I tried DBCC FREESYSTEMCACHE ('ALL') but always the message

I'll suggest that doing a restart isn't the right thing to do at this moment. The reason why you want to shrink TempDB is because it (apparently) got way too large. Some "runaway" query with an "accidental Cross Join" (think "many-to-many" and maybe has a GROUP BY or DISTINCT in it to get rid of "duplicated rows") is the likely cause in cases like this. And, chances are, it's still running as indicated by the locked "work table page".

I'm on my way to work and don't have access to the query(s) that cause the rampant growth but if you don't find out what the query is, you may be doomed to having to do this over and over in the future. Worse yet, if it is a currently running query, the system will be caught up in doing a ROLLBACK right after the restart. You REALLY need to find out what the cause was.

Like I said, I'm on my way to work... hopefully someone can cough up the query to find out what cause the growth.

--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 (12/12/2011)
Like I said, I'm on my way to work... hopefully someone can cough up the query to find out what cause the growth.

The DMV I described above will say what sessions are using TempDB and how much. If there's a query abusing TempDB it'll show there. Query sys.dm_exec_requests, cross apply to sys.dm_exec_sql_text and filter on that session_id and it'll show what query's currently running.

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