sort_int in tempdb
Hi:
Can anyone explain why I am seeing the following in my tempdb db. This transaction has been open for 3 hours and it took my tempdb log space. I had to kill the spid to rollback the transaction but i was wondering if there is a worka round to solve this.
A input buffer on this spid showed a sproc being called and I am wondering if my select statements in the sproc were not using correct indexes and thats what might be causing this. Can anyone from the community please confirm.
Transaction information for database 'tempdb'.
Oldest active transaction:
SPID (server process ID) : 112
UID (user ID) : -1
Name : sort_init
LSN : (128:150438:113)
Start time : Jul 30 2006 9:50:55:503AM
Thanks
AK
[772 byte] By [
Ankith] at [2007-12-22]
if you use 'group by, order by or derived table' in your sproc, tempdb is needed as working space. Thus, it will grow to accomodate your query.
oj at 2007-8-30 >
I do use order by and I have no way to get around it. I also use table Variables instead of temp tables. since I cant use non clustered indexes on table variables, do you think that the query is not using indexes and thus filling up my tempdb log. I have the problem occur again and again every couple of days and I have to kill the spid.
@tb will I/O to disk and use tempdb for its storage. So, with the combination of 'order by' and @tb, tempdb will grow.
Without knowing your data and query, it's hard to tell. In general, tempdb is shared by everyone, you'd want to make sure it resides on its own disk/spindle. Also, try to make your transaction as short as possible. If you must run a long transaction, try to force a manual checkpoint (see book online for details on checkpoint).
A few KBs for you to check out:
http://support.microsoft.com/kb/328551
http://support.microsoft.com/kb/224453
http://support.microsoft.com/kb/305977
oj at 2007-8-30 >
