Sql-server – SQL Server : low disk space impacts CPU

High CPU in SQL Server is very often caused by poor indexing

Unfortunately, SQL Server 2000 lacks the tools of later versions to track these down easily

Saying that, if you run SQL Profiler you will be able to find high CPU queries and start looking at query plans to work out what indexes are missing

If you've dropped all the tables, you could just drop the database and re-create it. It will be created with the default sizes and growth rates as defined by the model database. These may not be ideal (I don't find the defaults particularly useful in most scenarios), but you can change them.

Unless you have a lot of things set up on this database (like Service Broker, a ton of users, etc.), I think that is less cumbersome than getting into the habit of growing/shrinking growing/shrinking growing/shrinking. You should put the database on a drive that has enough space to accommodate the data you're going to generate, without having to shrink all the time. Shrinking and growing are both expensive events, especially on slow I/O.

Also, to prevent the log from growing to 9 GB (that seems pretty ridiculous for the data you describe), you should either be full recovery and you need to perform more frequent backups and log backups, or you should switch to simple recovery. More details here - it's long but please read it all.