April 19, 2010 at 12:47 am
Hi,
We had a process introduced into our environment, which causes tempdb log file to grow unchecked until it fills up the disk space.
Just wondering what are the implications of this? I understand when the process finds out that there is no more space in tempdb log, it fails and rolls back, freeing up the space in the log file.
How long it would take to release the space in the log file after the process failed? Does some of the space get released immediately or does it wait until the entire rollback process is completed? It takes about and hour for the process to run and fill up the disk.
Thanks.
April 19, 2010 at 2:06 am
It sounds as if either the process which was introduced is badly designed, meaning it is using an unnecessary amount of temporary tables/temporary data, or the diskspace available to the tempdb is not sufficient to manage the query as it is executed.
If it fails and rolls back every time, you'll have to consider either stopping or rewriting the process, or increasing the disk space available to the tempdb log (move it to another drive?) but if the query itself is the issue, then it will probably do the same thing on a larger amount of diskspace.
The main implications will be performance related to any other databases/queries running at the same time as this process is running.
It will most likely be creating disk queues and your I/O will be affected, creating a bottleneck for regular transactions.
You should have your tempdb sitting with simple recovery mode, as it is only managing temporary transactions and until anything is being committed to the main database in question there is no need to roll anything back. The transactions being rolled back will be in the log of the database your process is running against, and NOT the tempdb.
tempdb will be used to some extent for managing the rollback process though, so you will likely see a decreased performance continuing until it's finished.
April 19, 2010 at 4:03 am
To answer your question; if your waiting for tempdb to release space back to the os, you'll be waiting a long time. Cause it wont.
If you need to release space used by tempdb back to the os, the best thing to do is restart the sql instance which the tempdb belongs to.
When sql restarts the tempdb is reinitiasised at its initial set size.
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 19, 2010 at 6:42 am
That's a tough one, it really depends on a lot of factors, but a rollback frequently takes about as long as the orginal transaction. Sometimes it takes less time, and sometimes, it takes more.
The real problem is that any other process that are running at the same time, and making use of tempdb and it's log, are writing entries into the log that will prevent clearing the log until those queries are done, not just the long running one.
It's not as big a problem in 2005, but in 2000 you could bring the server down by filling up drive space, especially with log files, so it was not a good thing, ever. I wouldn't be quiet about this one. I'd suggest you get on top of the offending query as fast as possible, or add more drives & space for tempdb or both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 19, 2010 at 5:43 pm
Ok, I know that this query is bad and needs to be redesigned. I also know that the tempdb will not release the space back to the OS, which is fine.
My question was about the impact of such situation. When a query hits the disk space or the tempdb log file limitation, for a short moment there is no space in tempdb log, so no other query which needs to write to tempdb log will be able to run, which is more likely to impact some large reports which create some temp tables and update them.
So my question was how long the "no space in tempdb log" period is going to last. If there is nothing to rollback in tempdb, then some of it gets released immediately and any other query will be able to run?
Thanks.
April 19, 2010 at 7:30 pm
Roust_m (4/19/2010)
Ok, I know that this query is bad and needs to be redesigned. I also know that the tempdb will not release the space back to the OS, which is fine.My question was about the impact of such situation. When a query hits the disk space or the tempdb log file limitation, for a short moment there is no space in tempdb log, so no other query which needs to write to tempdb log will be able to run, which is more likely to impact some large reports which create some temp tables and update them.
So my question was how long the "no space in tempdb log" period is going to last. If there is nothing to rollback in tempdb, then some of it gets released immediately and any other query will be able to run?
Thanks.
It's going to vary from situation to situation, but the potential for it to have very large impact and take tempdb offline for a considerable length of time is high. It's not assured every time, but it's likely to happen often enough for your users to notice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 19, 2010 at 9:17 pm
Grant Fritchey (4/19/2010)
It's going to vary from situation to situation, but the potential for it to have very large impact and take tempdb offline for a considerable length of time is high. It's not assured every time, but it's likely to happen often enough for your users to notice.
Thanks for this. I am also wondering what are the mechanics behind the worst situation, when the tempdb is "offline" for some time. Like what exactly is happening at that time?
Also, why, when there is a transaction like this I can not get the properties window for tempdb? Is it because tempdb log is full or for some other reason?
April 20, 2010 at 5:54 am
Roust_m (4/19/2010)
Grant Fritchey (4/19/2010)
It's going to vary from situation to situation, but the potential for it to have very large impact and take tempdb offline for a considerable length of time is high. It's not assured every time, but it's likely to happen often enough for your users to notice.
Thanks for this. I am also wondering what are the mechanics behind the worst situation, when the tempdb is "offline" for some time. Like what exactly is happening at that time?
Also, why, when there is a transaction like this I can not get the properties window for tempdb? Is it because tempdb log is full or for some other reason?
Think about everything that the tempdb is used for and you'll have your answer for everywhere it can cause problems. Obviously, anywhere you have a temporary table, you'll get issues. But you'll also get issues anywhere a table variable is used. You'll also have problems with any query that has an ORDER BY statement. Queries that need to create a hash table in the execution plan will fail if the hash can't stay completely in memory. Index rebuilds will fail. The list goes on and on. For good or ill, tempdb is a very integral part of SQL Server and taking it offline for any reason has far reaching impact that your users will notice.
And yeah, you can't browse through it on the gui because it's full.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply