July 7, 2003 at 3:19 am
Hi!
A MS SQL 2000 SP3 has ~15 user databases on it. Some are 7-17Gb. A tempdb log is on a mirror 18Gb with all other databases logs. Tempdb log before the last server restart was ~12Gb. Is it normal? Are there any other ways to restrict its growth (other than server restart)?
Thanx
July 7, 2003 at 6:17 am
I have found the following link helpful in trouble-shooting this problem in the past:
http://www.wamoz.com/giant_tempdb.asp
Joseph
July 7, 2003 at 7:09 am
I am afraid this is not the case, because the tempdb itself is not growing. It started today from 300Mb for data and 500Mb for log. Now it has 300Mb for data AND 8Gb for log. Just in one working day! I am affraid it will eat the whole disk before the work time is over!!!
July 7, 2003 at 8:05 am
One possibility:
You have a user that is dropping and recreating their database on a daily basis.
Why do I think this?
The TEMPDB is used to keep track of changes to databases until they have been committed. But once committed the free space doesn't get truncated. Due to the TEMPDB space being taken up and the size of the user databases, I think someone is dropping and replacing their database. Restoring a large database could cause the TEMPDB growth that you are seeing.
Should you be worried about it?
Yes and no.
Yes. Someone is doing some intensive work on the server. It's got to be tying up resources.
No. As long as the space is being freed up (check to ensure that it's showing most of the 18GB as FREE space), it's usable by other users.
-SQLBill
July 7, 2003 at 8:35 am
I am sure, that no one did this today. This is my authority.
But for this problem I found a very unusual cure:
I limited the grouth rate of tempdb log by 50Mb at a time. Once it reached 99%, it was grown by 50% and then freed up about 97% of space. So now it is 8Gb (as before) and 14% used (filled up a little again)
I still cant understand why the log is not truncated...
July 7, 2003 at 8:36 am
Sorry, a little misprint:
not 50%, but 50Mb
July 7, 2003 at 11:24 am
The TEMPDB is not automatically truncated. You can truncate it by stopping and restarting the services.
I believe this is a 'feature' of MS SQL Server. Look at it this way...
I (TEMPDB) needed 8 GB of space for one or more actions. When they were done, I emptied the space, but I'm going to hold it as free space for the next time. That way, when the actions happen again I don't need to use processor/memory to expand - the space is already available.
So, in other words...why truncate the unused portion? The unused portion will be used for future actions and TEMPDB won't need to expand again unless it needs more than 8 GB of space.
-SQLBill
July 8, 2003 at 12:17 am
What will happen if the size of the tempdb log goes close to 18Gb? The server will stop, because it will not allow other databases logs to grow. If I left the growth increment equal to 500 Mb, it would grow to 18 Gb by the end of the day, that is not very good.
I am thinking about limiting the size of tempdb log by 12-14 Gb, there is space for other databases logs. But what will happen if tempdb log reaches its limit?
July 8, 2003 at 4:58 am
Hi,
case you are a subscriber to SQL Server Mag, there is an article on tempdb in the July edition. Maybe it is also available online?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 8, 2003 at 5:39 am
2a5xo3z1:
Not yet... Will get the first issue in august or september. I could not find this article online. Will try to find something about it on Microsoft site...
July 8, 2003 at 6:20 am
quote:
2a5xo3z1:Not yet... Will get the first issue in august or september. I could not find this article online. Will try to find something about it on Microsoft site...
When you get your first issue, try this link
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39157 or
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39158
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 8, 2003 at 6:25 am
If TEMPDB reaches it's limit, pretty much everything stops. TEMPDB is used for just about everything SQL Server does.
Do you have a way to move TEMPDB onto another set of drives?
-SQLBill
July 8, 2003 at 7:10 am
The tempdb itself is not causing problems, its log does. It is located on a 18Gb mirror with other databases logs. The other logs consume ~1,7Gb of space (about 100Mb per database on the average). Suppose I move tempdb log on another disk, even 36Gb size, what if it will eat all space again?
I think we face some abnormal behavior: if we set the database log grouth rate to 500Mb, it will grow to ~18Gb in one working day. If we set it to 50Gb it will grow to ~1Gb during the same time, regadless of the space need. The more often it grows, the more resources it takes to grow, the less often, - more space. If I create the tempdb log 8Gb in size from the begining it will not free any space until it eates this space and has to grow. If I create a small (~100Mb) tempdb log and set a small (~50Mb) increment for its grouth, then it frees space quite often and does not grow so much...
July 8, 2003 at 7:22 am
Another thougth:
Understanding the impact of periodic file grouth I work we a server that is not recomended to restart even at night: hundreds of users, so it will be more acceptable, to let it grow little by little. On the other hand does a small file size means less overhead to work with it?
July 8, 2003 at 7:24 am
I think it may be a good idea to check what kind of queries are running on the server. I believe some of these queries may need lot of tempdb space which is not good. See if some stored procedures are creating huge temp tables.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply