Solved

The tempdB storage size token by the query that stick in the tempdb

Posted on 2013-06-07
38
668 Views
Last Modified: 2013-07-10
Dear all,

Right now we use the following query to find out which abnormally stay in the tempdb for a long time and keep use up tempdb's log file space.  

SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" ,
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed"
FROM master..sysprocesses WHERE loginame= 'yyy' and  open_tran > 0

AFtre we found one then we will kill <spid> to kill the query.

But this script is not perfect enought, how can we change it to also include the tempdb storage space/size the eat up by the query shown in the output ?

DBA100.
0
Comment
Question by:marrowyung
  • 25
  • 13
38 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39231421
Well, the total space consumed on disk from tempdb probably wont change even if the temp tables no longer exists (dropping a temp table doesn't immediately recover disk space - that happens subsequently during "quiet" periods).

When a session is dropped or disconnected then the temp table should ultimately be dropped, but still, from time to time we can get "orphan" temp tables.

Have a look at the script : http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39252158
mark_wills,

hey, long time no see, how have you been ?

what I actually want to see is if I use the query I post above and found out something, then I hope I can a tools/script to intergrate with the script I post above to found out how many tempdB space it takes, do you know any?

are you using that script also ? any problem you can see ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39252431
Been well, thank you for asking, and trust you too are doing well... And yes, it has been a bit too long :)

Don't know of any script off the top of my head that does both...

Would normally start with DBCC OPENTRAN or sys.dm_exec_requests for those slow/ long running queries

You could possibly link to sys.dm_tran_session_transactions  (using session ID) and then sys.dm_tran_database_transactions (using transaction ID) checking for just the tempdb database (database ID = 2).

or extending your own code a little... you could check sys.dm_db_session_space_usage to show how much tempdb space is being used
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
,u.*
FROM master..sysprocesses p
left outer join sys.dm_db_session_space_usage u on u.session_id = p.spid
WHERE loginame= 'yyy' and  open_tran > 0 

Open in new window


But tempdb is used for a lot of different things, not just temp tables so if the goal is purely temp tables, then almost have to identify them from the script / sql handle and that too might be very hard to identify. Getting the actual table name so a different session can drop or cleanup is going to be hectic.

Think of a procedure creating a table #temp and then think of several executions of that procedure. Now within the procedure it is only ever known as #temp. But if you look at the temp tables in tempdb they all have different suffixes and will be challenging matching the correct one (assuming you can identify the actual name).

But again... If you kill a process and that process has a temp table then the temp table should also be automatically dropped just wont release / shrink tempdb automatically.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39255463
I execute the result from the script you rewrite, it seems that I need to time the page count by 8K bytes then we can know the total tempDB space that query takes ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39258624
Yes.

If you look at http://msdn.microsoft.com/en-AU/library/ms187938.aspx it says that size is given in number of pages. So yes, need to multiply to get bytes

Just be aware that you should subtract the deallocated count to show current consumption. And for very large objects, there can be a deferred drop which might give the wrong impression - but - the DMV will still show a large allocated size.

Might be worth having a read of : http://msdn.microsoft.com/en-us/library/ms177495(v=sql.105).aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39271037
so if I am now trying to use separte script for that, then I only need to find out hte SPID and plug that in the second script.

Then what will that script be, that script supposingly give more information.. right?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39272686
Yep, that's right.

But be aware of those deferred deletes / deferred drops as mentioned above... And the table naming problem as previously described.

You should really familiarise yourself with : http://technet.microsoft.com/en-us/library/cc966545.aspx it has a couple of very useful scripts in there too.

So, the next part of the question, "what will that script be"

That is very difficult to answer because the two are not really related. When you kill a spid (now named session_id from 2005 onwards in various tables / views et al) then it will release (drop) any temp tables. Again there is the possibility of deferred drops.

But that doesn't automatically shrink the tempdb, and if deferred, it will be locked out until it is physically dropped anyway.

Then there is your assumption that the temp table will hang around after the spid has been killed, but, you wont be able to identify that temp table specifically belonging to the script generated by that spid - the name used in the script is actually generating a unique name in tempdb and while prefixed the same, it will have a pile of underscores and a unique number as part of the real / actual name....

If you kill the spid , then you can no longer find the workspace used by that spid. And once killed, that integer value is released and could be reassigned to another session. Generally system spids are < 50 (but can sometimes be greater), so you can ignore those ones.

So, the approach I would be inclined to use would be to use your original query, coupled with the additional left outer join to the session_space_usage DMV (as shown above) to see if there is a big demand on tempdb. And that demand is not restricted to temp tables, but is an indicator.

Then, if it was really concerning that somehow tempdb wasn't being cleansed, I would use a similar script to that "orphan" example to see if there are any orphan tables that match the table prefix identified by the script and by the datetime of the original query, but would be potentially difficult, perhaps dangerous, and more likely would store the results to check for orphans in the following day(s).  

Trying to link the session_ID that you want to kill to a temp table that needs manual intervention / removal because the killed process didn't cleanse is not something I have ever tried to do and doubt that it can be done easily or effectively. So, I guess that really answers the second part of your question "what script" = there isn't one that I would use.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39287748
probably the need to knows this is not that necessary and we just need to kill the troublesome process.

As what if once we kill that process that out of space problem is appear?

Then the total amount of space token by that sPID is clear enought.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39292518
"That is very difficult to answer because the two are not really related. When you kill a spid (now named session_id from 2005 onwards in various tables / views et al) then it will release (drop) any temp tables. Again there is the possibility of deferred drops.

But that doesn't automatically shrink the tempdb, and if deferred, it will be locked out until it is physically dropped anyway."

Yeah, it don't shrink automatically !! after killing that I still need to shirnk the log. So I what I am planning is to do daily log shrinking only for tempdB.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39292692
mark will,

are you using this :

http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/

righ now without changin anyway?

any problem on using this ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39296245
from this link:

http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/

what will be the tempdb.. in "DROP TABLE tempdb.." about?

I can implement this SP but just want to make sure that:
1) it don't kill tempdB.
2) it don't delete anything in other DB, speciall user DB.

anyone try this before and what is the result?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39298494
No, I don't use exactly that. Once in a while I use a similar routine to highlight any orphans and then go investigating rather than allow the routine to clean up.

So, I don't have an equivalent "files to keep".

If there is anything that needs cleansing, it normally means there is a problem somewhere else and try to use routines to help identify the root cause of issues and address those.

Having these types of routines is good to have in the arsenal, but then they need to be used to expose the real problem rather than mask or hide the amount of work needed to keep tempdb clean.

Also, try to make your tempdb the proper size. It is a bit harder now (since 2005) because it does get used for a few more things, but shrinking to minimum and then having to grow all the time is not good.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39298542
Oh and that drop table business...

The statement is :

SET @SQL = ‘DROP TABLE tempdb..’ + @Name


Which is building some dynamic SQL appending the name of the table (in @name) so you end up with a string something like :

DROP TABLE tempdb..#tmp__________________________________________000000003ED

Of course there are a lot more underscores in that name.

Having the prefix of tempdb in the table name explicitly tells SQL what database to delete from, it wont actually try to remove anything other than that exact table in that location.

The full naming convention of any table is a multi-part name :

server_name.database_name.schema_name.table_name

in the above, it is the current server so is omitted from the name. Similarly, it is using the default schema name so doesn't put in a value for that part (but because it is in the middle we still need the fullstops). And because the command is "drop table" then the command must point to a table (and not a DB or any other object). If it cannot find a table with that name, then you get an error e.g.

DROP TABLE tempdb..foobar

-- will generate the message :

Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'tempdb..foobar', because it does not exist or you do not have permission.

Open in new window


The crucial part in that routine is getting that "where" clause for the cursor correct for your own purposes. At the moment it is checking against "files to keep" and table is at least 12 hours old. You could add in your own criteria there.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39299012
"No, I don't use exactly that. Once in a while I use a similar routine to highlight any orphans and then go investigating rather than allow the routine to clean up. "

What kind of routine ? please show me as I want to learn from you on identifing the root cause of issues

"Also, try to make your tempdb the proper size. It is a bit harder now (since 2005) because it does get used for a few more things, but shrinking to minimum and then having to grow all the time is not good. "

what is proper size, this the main problem/question, I let it run in default start up size and let it growth. we can't shrink the tempdb mdf as from to time we can't shirnk that as the size didnt' decrease but the log.

what is few more things you means here? I agree that probably because of this, SLQ 2005 sseems give us more stable tempdB.

In the script:
" WHERE ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') AND (TempTableToKeep.DateToDelete < GETDATE())) OR
 ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') AND (TempTableToKeep.DateToDelete IS NULL))"

What is U for ? user object but not system object and we just need to find out user object.

"If it cannot find a table with that name, then you get an error e.g.
"
it should not appear by this script I think ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39299206
one thing, it seems that once all object in the tempdb last for less than 12 days is in the TempTableToKeep table, the drop is not refer to object NOT in the TempTableToKeep table, right?

then how can it drop the right objects,

then it seems that there are no clean up of the TempTableToKeep table ad this table keep growthing, right?

we add trunate logic for this table once this SP done ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39299548
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39301602
as  according to:

http://www.mssqltips.com/sqlservertip/1473/killing-a-spid-and-checking-rollback-progress-in-sql-server/

and

http://msdn.microsoft.com/en-AU/library/ms173730.aspx

it seems that there is a way to delete this kind of object:

"terminate an orphaned distributed transaction (session ID = -2) with a UOW "

why session ID=-2?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39301605
how can I obtain the UOW <xxx> and try to delete using hte UOW? it seems that for example:

kill 98 WITH STATUSONLY ;

shows SPID instead of UOW number.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39301653
right I just run that :

http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/

when I execute the SP:

exec sp_DropTempTables
go

I see this error message:

Msg 16924, Level 16, State 1, Procedure sp_DropTempTables, Line 17
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.


what is going on ?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 39303035
Phew, a few extra comments here...

Let me try to address one by one.

1) The "U" is the object type and means User Table.
2) The additions to the "where" clause might include len() of table name, the user created temp tables are 128 characters (certainly a lot more than the 9 chars of system generated). Could also include checking the '#' prefix of the temp table name.
3) The routine checks against temptabletokeep which is a manually maintained table of user objects that we "know" might exist and "know" they will be handled normally. The routine shows the creation, but should not be considered part of the daily / weekly script.
4) it is checking for user objects in tempdb that are 12 hours old (or older), not 12 days.
5) it does check for entries in temptabletokeep and will drop if not there, or, if the datetodelete has been populated with a value less than "today" (which means if you really want to keep a table set a datetodelete way off in the future).
6) My own routines are very complex and part of a complete management regime which is not easy to dissect because of all the myriad of interrelating aspects.

7) session id of '-2' is a fake number reserved for a distributed transaction. The distributed transaction doesnt actually have a real session, so is given the value of '-2'
8) Distributed transactions start somewhere else, and the only way to identify them is via the GUID - which is quite a different value - it is a 16byte hex value of the (string format) xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx so it is immediately different to look at from the integer session_id
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39303066
OK, don't always run the entire script. The temptablestokeep and the drop/create SP are really just a once off (unless you really do need to recreate).

In the select part of the "declare cursor" you need to get rid of the *. When you fetch into the variables, the number of columns in the cursor has to match the number of variables, and really don't need the @owner either. So, just select the name column and just fetch into @name
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39306444
"OK, don't always run the entire script. The temptablestokeep and the drop/create SP are really just a once off (unless you really do need to recreate). "

no I didn't and I just run that one if I deleted/drop the SP. I never do this as what if the script is not clean enought and taht's why I come here for.

"In the select part of the "declare cursor" you need to get rid of the *. When you fetch into the variables, the number of columns in the cursor has to match the number of variables, and really don't need the @owner either. So, just select the name column and just fetch into @name "

so what should I do ? this:

 SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.crdate

Open in new window

and delete this:

DECLARE @Owner AS VARCHAR(100)

Open in new window

?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39306499
I found this:

http://gaptheguru.wordpress.com/2012/02/16/cleaning-up-tempdb/#comment-1882

This is exactly the same and I can't see why.

the auother said this is workong on SQL server 2005 and SQL 2008 but hasn't try it on R2, what is the problem?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39306515
Nearly...

instead of :

SET @Cursor = CURSOR SCROLL FOR
 SELECT    tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
 FROM    TempTableToKeep
 RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
 WHERE    ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete < GETDATE())) OR
 ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete IS NULL))
 OPEN @Cursor
 FETCH FIRST FROM @Cursor
 INTO @Name, @Owner

Open in new window


try

SET @Cursor = CURSOR SCROLL FOR
 SELECT    tempdb.dbo.sysobjects.name  --, tempdb.dbo.sysobjects.*
 FROM    TempTableToKeep
 RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
 WHERE    ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete < GETDATE())) OR
 ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete IS NULL))
 OPEN @Cursor
 FETCH FIRST FROM @Cursor
 INTO @Name   --, @Owner

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39306530
once I comment that:

SELECT tempdb.dbo.sysobjects.name--, tempdb.dbo.sysobjects.crdate

Open in new window


and

 INTO @Name--, @Owner

Open in new window


when I execute that I see a lot of that:

use tempdb; DROP TABLE tempdb..#0519C6AF
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#0519C6AF', because it does not exist or you do not have permission.

Open in new window


what permission I need, already sysadmin..

it seems that once add to by doing this:

 select top 10 * from tempdb.dbo.sysobjects
 WHERE ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') 
 ) OR
 ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') )

Open in new window


we can see that the name is changed to something we don't udnerstand instead of a name:

e.g.: #0519C6AF

and therefore the error above, so it is not persmision thing is the name realy can't find.

I think that;s why when creating the table it change to COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL but it seems doesnt' works.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39306738
mark_wills,


we have the same minds there and the above post is the error I got ,the name has changed to a number... it can't find anyting like this to drop and there for an error raised.

e.g.: #0519C6AF
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39307406
The 9 character temp tables are system generated, not user generated.

If the user invoked routines are creating temp tables then they will have a long name (128 characters).

In SSMS try this quick quiz...

create table #tmp1 (id int, dt datetime)
create table #tmp2 (id int, dt datetime)
go

select * from tempdb.information_schema.tables

Open in new window


You will see quite a few of the 9 character (hex) temp table names and you will also see the two new ones with a pile of underscores trailing the name...

Now if you do "select @@spid" in that same window, and write down that value (lets pretend it is 99)

Then open another (new) query window... then try :
kill 99 -- where 99 is the spid of the other window -- MAKE SURE IT IS THE CORRECT SPID.
go

-- wait a few seconds

select * from tempdb.information_schema.tables

Open in new window


you will see that the temp tables #tmp1 and #tmp2 have been automatically cleaned up.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39307467
ok, right now the problem still be, why after remarking 2 x line, when executing it, it will say can't drop ?

can't see what has be to fixed in order to get it right.

By getting this one, my another ticket about kill spid probably can be solve as it is just abnormal and I just drop this kind of orphaned object.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39307616
Because those 9 character temp tables (whilst "user tables") are rather special and used by the engine.

tempdb is now used for a LOT of things, but from what you have said, we are interested in user created temp tables which are longer than 9 characters...

If there is a lock or an active transaction on the table, then you wont be able to drop anyway.

is all this to do with SPID 62 ? You will have to resolve that first....
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39309321
"is all this to do with SPID 62 ? You will have to resolve that first.... "

I have 3 x more spid id about the same thing, the last sort is to restart that SQL instance.

but now it seems that we come up with a situation that the tempdb clean up SP:

http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/

doesnt' work as the error comes up AND this SP do not have logic that go handle this, or how can we explain why, evne I follow your change on the script, the SP when executin, doens't work ?

":If there is a lock or an active transaction on the table, then you wont be able to drop anyway."

This SP can't handle this problem but this kindo of lock should be normal, as this is a computer science thing, etc.

"tempdb is now used for a LOT of things, but from what you have said, we are interested in user created temp tables which are longer than 9 characters..."

but the SP can't handle this.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39309559
No, it cannot handle everything in tempdb - unless that temptabletokeep has been populated with all the right things. Or, you only check for objects that might be able to be deleted (e.g. changing that "where" clause).

And no such thing as "should be normal", if there are locks, then your process will hang around until the drop can gain exclusive access.

Just because there is a temp table, doesn't always mean that anyone can drop it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39309618
"No, it cannot handle everything in tempdb - unless that temptabletokeep has been populated with all the right things. Or, you only check for objects that might be able to be deleted (e.g. changing that "where" clause). "

yeah, this is one of the thing  I am thinking, how can we let the temptabletokeep growth without those error message rising ?

or just let it be until it works ?

What to change about the where cause ? the whole thing needs to change then as that one is the key part of the script !

"And no such thing as "should be normal", if there are locks, then your process will hang around until the drop can gain exclusive access. "

Then I think I should be use that but a shrink in transcation log regularily as this seems ok for me to just shrink.. this script can't even work as normal without any error messag.e
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39309631
the sp_locks don't show anything about this process, hard to solve.

it is showing TAB IX lock on objid xxxxx ,that's it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39309670
by that link:


http://technet.microsoft.com/en-us/library/cc754134(v=ws.10).aspx

I can't find out any transcation reatled to any local or clustered DTC.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39313150
"6) My own routines are very complex and part of a complete management regime which is not easy to dissect because of all the myriad of interrelating aspects."

I respect that very much, thanks.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39313152
thanks Mark_wills even the script for tempdB object is not working at all.

I see the author has the same question from someone else, it doens't work for SQL server 2008 R2 seems.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39313291
Eh ? Sounds strange that it doesn't work for R2... But if that has been confirmed, then who is to argue.

I guess your strategy to manually intervene / control tempdb when needed is pretty much like the rest of us...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39313365
basically I can simply just don't use that SQLmonitor.exe, but I don't think it is the problem of that software as only one SQL server prblme but not all.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now