July 16, 2003 at 6:57 pm
I recently managed to create the Full tempdb Log condition:
"log file for database 'tempdb' is full. Back up the transaction log (etc.)..." for a SQL Server 2000 RTM instance. I have extended the size of the transaction log so this is not a problem anymore, but I'm in post-mortem trying to understand why it happened.
The Error Log showed my connection as the offendor. I was executing commands, back to back, from the Query Analyzer and one of them caused the Log full condition. I'm open to ideas for which query would have been writing to the tempdb Transaction Log to cause its cup to floweth over.
Query1 was a SELECT statmement on a rather wide 3 million row table, ordering the resultset by a non-indexed column. MYTABLE has a single Clustered 6-key index and a single one-key nonclustered index. After about 10 mins, I cancelled the transaction prior to completion. FYI, I was trying to create a 'Worst case' query to test server response. I think I accomplished that goal:
Select * from MYTABLE
ORDER BY XITEM
Query 2 ran to completion, giving me a single row of output:
DBCC SHOWCONTIG ('MYTABLE', 1) WITH TABLERESULTS
Other factors:
1. This was done while user load was relatively light on an OLTP environment, so others may have been in on tempdb at the same time. I don't have a Log reader to see exactly who was doing what.
2. Plenty of unused space available in the tempdb Data file (50 MB used/4950 free)
3. Log size was 500 MB with 15 MB Autogrow.
Thanks in advance to all who reply.
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
July 17, 2003 at 5:09 am
First keep in mind that tempdb is design to automatically flush to make room for other queries use otherwise you would be doing a manual flush everytime a data set had an order by clause. In otherwords it is set to truncate on checkpoint (sql7) or simple recover model (sql2k) and when the process was ended or completed the data was removed.
The other factor is the size of the data you were sorting. When order by is used TempDB is used to handle the sorting from the dataset. Using a non-indexed column can make this process slow and tedious but you have read and writes on the tempdb also adding to the overhead. So take a look at the size of your table in EM and keep in mind if you sort all the rows that means TempDB actually grew to at least that size and maybe a little larger because the table is created in a heap then read back in the new row order which is the sorted data.
And the final factor I can think of you have to account for is any other load on the box that would have also utilized tempdb at the time you ran into this. If someone else was running an order by query, query with subquery, temp tables, etc then you alo have tht size added in.
Hope this helps.
July 17, 2003 at 10:45 am
Makes sense, thanks. I guess this would also explain the tempdb Transaction Log 'hangover' I've got, almost .5 GB of Used space that won't release. Now I can be certain it is the unfinished transaction I killed about halfway through the Sort. Total table size is 550 MB. I'll consider restarting the instance this weekend to free that up.
What is surprising to me is the use of the tempdb Transaction Log for the sort. That's the part I want to really understand, so I'm going to try to spit back a summary of your description as I understand it. Corrections would be welcomed. Here goes:
The ORDER BY on the unindexed column caused the table to be written to tempdb (data) as a heap, and this write is recorded in the tempdb Transaction Log, just like any row creation event in a standard database table?
Interesting. I have to say I've never thought too hard about it but it's clear that tempdb utilizes the fundamentals of the Storage Engine and Transaction Manager just like any other DB.
Thanks again for your brain dump. Much appreciated!
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply