Hi all
I am using Sql Server 2000. My application deals with lot data in the db(some tables have data in the range of 34 million records to 50 million records). When i run my application the tempdb grows upto 70 GB and i am getting the error Disk out of space.
How to control this tempdb growth?
Note - No Index or Keys are defined on those tables.
Removed those keys to improve the performance of another application which uses the same database.
Sankar
I think you gave the solution yourself. Removing the indexes will cause a lot of hash joins which in its turn causes tempdb usage.
You really should consider using indexes. The only time indexes can cause you grief is when loading or inserting massive amounts of data and you have lots of indexes. If this is not the case maybe your storage subsystem is simply not well scaled for the database.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Tempdb is the workarea for the instance. u need to findout what consume TEMPDB more. Whether there is any open transaction(DBCC OPENTRAN) . Droping of index is not a solution at all rather you must see the query and create proper indexes. I believe your tempdb Transaction log is growing not the data file . in that case you can shrink the log also.
Madhu
|||No i dont have permission to create index now as the same database is used by other applications also ....|||Then you will probably have to live with the fact that you need a big tempdb.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
No comments:
Post a Comment