Two days back we upgraded from SQL Server 7 to 2000.
After upgrade we are getting tempdb problem. The c drive where tmpdb data and log files are stored has 4 GB. The other database files are stored in E drive. Since the tempdb is set for unlimited growth, the C drive gets full. All the server and db settings are same as sql 7. We have also ran the sp_updatestat after upgrade to optimize the performance.
Is there any way to know what (query) causing the problem? How to pinpoint the problem with 75 user system? But why it was working in 7.0 and not with 2000 ?
Also once c drive is full due to tempdb, is it possible to free the space by truncating the db without restarting the server.
We tried running...
.... nothing worked.
First of all , Its not a good practise to install any databases (System or User) to install on "c:" drive.
Since , its already grown too large and you want to shrink it.
1. Make sure that there is no large transaction going on which is to be sorted or sorting....
2. Use DBCC SHRINKFILE to shrink the tempdb file.
But, I would like to suggest you that always make the system to take care of shrinking. (By default its Auto Shrink, so it will take care of it )
Whether its 7.0 or 2000, tempdb behaviour concept remains same.
Setting tempdb to "Unlimited Growth is good" , so dont change that.
Make sure that "tempdb" is not set to grow by more %. (For example, if tempdb is set to 50% grow, then at one point if the log size 1GB the next growth size will be 500MB which will make it 1.5GB and so on... But Dont set to too low also. If you set too low , then it may have to grow lot of times by small in size which decreases performance.)
Dump Transaction will not work, since this tempdb is always by default set to "Truncate Log on Checkpoint". Its meaning less to do that.
Concept on Optimizing tempdb Performance:
General recommendations for the physical placement and database options set for the tempdb database include:
1. Allow the tempdb database to automatically expand as needed. This ensures that queries that generate larger than expected intermediate result sets stored in the tempdb database are not terminated before execution is complete.
2.Set the original size of the tempdb database files to a reasonable size to avoid the files from automatically expanding as more space is needed. If the tempdb database expands too frequently, performance can be affected.
3.Set the file growth increment percentage to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small compared to the amount of data being written to the tempdb database, then tempdb may need to constantly expand, thereby affecting performance.
4. Place the tempdb database on a fast I/O subsystem to ensure good performance. Stripe the tempdb database across multiple disks for better performance. Use filegroups to place the tempdb database on disks different from those used by user databases.