tempdb database HUGE

[phil22046]

I was doing a routine inspection of my sql server machine and found that the tempdb database has grown to the point of nearly filling the Hard drive. I don't know what size it should be, but the last date on it is June 5th, 2007. it is 53 Gigabytes and the largest database is 24 gigabytes.

I don't remember what it is used for. And I am not sure what to do to fix the problem.

 

[ptheriault]

To fix the problem you will have to restart you SQL Services. That will re-create the tempdb.
What version of SQL are you running?

Here is some info for 2005.

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

SQL 2000

 

[SQLBill]

You could truncate the log and shrink the file, but the easiest way to clear the tempdb is to schedule a downtime and stop and start the SQL Server services. That will rebuild the tempdb to the size of the model database.

-SQLBill

Posting advice: FAQ481-4875

 

[ptheriault]

The tempdb database is a system database that contains temporary tables created by the server and by the user. The database is recreated each time SQL server is restarted. If it has grown to such a large size it is because at some point the system must have needed the space. You may want to consider placing it on another array. This will increase performance and help with disk space.

See the following link for more details.

http://www.sql-server-performance.com/tempdb.asp

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[phil22046]

How would I put this database on another Array? We have various sans drives available that have tera-bytes of free space. We have drive letter mappings to these areas.

 

[ptheriault]

Use the ALTER database statement as outlined in the link that is posted above. But you can not move the tempdb to a mapped drive it must be assigned to the server.

- Paul
- If at first you don't succeed, find out if the loser gets anything.