Tmpdb

Question

Tmpdb

jr_arantza

Grasshopper

Points: 20
More actions
February 11, 2007 at 3:39 pm

#62804

Hi to everybody,
Our SQL database runs under SQL 7.0 and Windows NT 4.0.
Until now our database was running without any problems, but suddenly the Tmpdb file began to grow strongly. I have the feeling that the main reason is because we are getting short of disk space for our SQL database.
We have two hard disks in mirroring where the windows NT is installed. Here we have about 1.7 GB free disk space but our tmpdb file grows until covers most part of it with serious risk of being blocked. In a second separate hard disk extension we have the log database, with enough space and under Raid5. And finally we have the data database in a third physicall separate extension, also with Raid5. This database is about 137 Gb but we have only about 7 Gb free.
I think the problem could be because we are short of disk space for the data database, which could run extra logs in the Tempdb database, and this could force to grow temdb log strongly in C partition.
Could anybody inform me about it ?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135663 More actions · Answer 1

the growth of you tempdb must be due to usage of your sqlserver !

Someone running huge queries, grouping-sets or sorting. Keep in mind tempdb is not persistent. Whenever sqlserver is started, the old tempdb entries will be cleared !

So tempdb cannot be used for recouvery reasons.

you could stop/start your sqlserver, so tempdb starts with it's original size, then start a trace or configure an alert to monitor tempdb growth.

If you're using an alert, you may want to start a job with it to start a trace for a short period. Then you can investigate the queries in the result.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Who am I ? Sometimes this is me but most of the time this is me

jr_arantza Grasshopper Points: 20 More actions · Answer 2

Thanks a lot for your information !

Yes, I knew that whenever you stop/start sqlserver, tmpdb starts with it's original size. So we could confirm that tempdb growth is totally independent of lack of disk space for database, couldn,t we ?

On the other hand, could you tell me how I could start a trace or configure an alert to monitor tempdb growth ? Does it affect to system performance ?

Thanks in advance.

Johan Bijnens SSC Guru Points: 135663 More actions · Answer 3

1) yes

2) that should be quit easy. At install time there are demo alerts created. They are disabled. If you didn't remove them, they should still be present.

One of them is called "Demo: Full tempdb" (9002) which responds to condition 9002 for tempdb.

If you enable it, you can use it;

If you create a job which starts a sqltrace for e.g. 10 minutes, you can have the alert launching it so you can investigate the tracedata later on.

Depending on the kind of trace you are starting and the data you want to capture, it has less or more impact to the system; That's why you should restrict it in time (10 minutes or so).

A standard tsqltrace hasn't got heavy impact. You may want to launch it yourself (sqlprofiler) and figure it out for your situation.